Strip non-digit characters - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Strip non-digit characters

Author

Topic

mowebdev
Starting Member

3 Posts

Posted - 2004-10-25 : 15:49:14

I need to be able to strip out all non-digit characters in the following query. The international phone number file has many variants of phone numbers, with spaces, brackets, dashes, all sorts of things.

SELECT MMUSB# AS MMUSB, MMCO, MMADDR, MMCITY, MMST, MMZIP, MMSTAT, MMTYPE, MMCTCD,

case when MMCTCD = 'USA' then MMFONE when MMCTCD = 'CAN' then MMFONE ELSE MIIP# end as MMFONE,

case when MMCTCD = 'USA' then '' else MAADDR end as MMADDR2

FROM CUSTOMER
LEFT OUTER JOIN USBADDP ON MMUSB# = MAUSB#
LEFT OUTER JOIN USBIP#P ON MMUSB# = MIUSB#
WHERE digits(MIIP#) = '234234123437'

This doesn't work. I tried using PatIndex() which some other posts suggested, but it doesn't seem to work with my AS400 system.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-25 : 23:38:59

http://www.nigelrivett.net/RemoveNonNumericCharacters.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mowebdev
Starting Member

3 Posts

Posted - 2004-10-27 : 10:19:46

The solution that worked for me was to create a function. Thanks for the help.

create function numsonly (aStrVal varchar(255))
returns varchar(255)
language sql
deterministic
allow parallel
returns null on null input
begin
declare retVal varchar(255);
declare i integer;
set retval = '';
set i = 1;
while i < char_length(aStrVal) do
if substr(aStrVal,i,1) in
('0','1','2','3','4','5','6','7','8','9') then
set retVal = retVal || substr(aStrVal,i,1);
end if;
set i = i + 1;
end while;
return retVal;
end

nr
SQLTeam MVY

12543 Posts

Posted - 2004-10-27 : 14:16:54

that'll be very slow for long or many strings.
It would be a lot faster to use patindex as in my examples so that you find the next non digit to update.
In this you should at least check for a valid string so it doesn't do anything if it doesn't need to.

hmm - which database are you using? oh as400 - wrong place for that
in t-sql it would be

create function numsonly (@s varchar(255))
returns varchar(255)
begin
declare @i int
select @i = patindex('%[^0-9]%', @s)
while @i > 0
begin
select @s = replace(@s, substring(@s, @i, 1), '')
select @i = patindex('%[^0-9]%', @s)
end
return @s
end
go

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources