Remove All but 0-9 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Remove All but 0-9

Author

Topic

epicphotoeye
Starting Member

4 Posts

Posted - 2007-05-29 : 13:41:01

I'm having a hard time finding an efficent way of doing this. Currently I use UPDATE to remove some symbols but this takes a long time and sometimes I'm going through records by the thousands.

Field 1
-74.493 b(45503)
-42.942 c(45560)

want returned

Field 1
7449345503
4294245560

netedk
Starting Member

11 Posts

Posted - 2007-06-05 : 11:56:11

UPDATE TableA
SET field1 = dbo.FilterNonDigit(field1)
WHERE ......

CREATE FUNCTION [dbo].[FilterNonDigit]( @Input varchar(256))
RETURNS varchar(256)
AS

BEGIN
If PATINDEX('%[^0-9]%', @Input) > 0
WHILE PATINDEX('%[^0-9]%', @Input) > 0
SET @Input = Stuff(@Input, PATINDEX('%[^0-9]%', @Input), 1, '')
RETURN @Input
END

Marketing Communications: http://www.realmagnet.com

Software Development:http://www.daksatech.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources