I need to strip all characters that are not a number from a string
variable in a sql procedure. There is not clear pattern to the
character the user can input.

Example input for the phone field
1. call me
2. 1 (555) 555-8888
3. please email
4. +049.890.9876
5. 0x0x0x
6. Work: (444) 123-4567

I need to strip the characters down to only a number so I can evalute
if a correct entry was made.

After stripping and checking above example numbers 2,4, and 6 would be
the only examples that would come back valid.

Any help would be appreciated.

Thanks,

hi alyx,

if i understand you correctly, you will have to make use of user defined
function.

ex:
--function

create function dbo.fn_numeric (@strn as varchar(8000))
returns varchar(8000)
AS
begin
WHILE PATINDEX('%[^0-9]%', @strn) > 0
SET @strn = REPLACE(@strn,SUBSTRING(@strn,PATINDEX('%[^0-9]%',
@strn),1),'')
RETURN @strn
end
GO


--sample data

create table t(strn varchar(50))
insert into t select 'call me' union all
select '1 (555) 555-8888' union all
select 'please email' union all
select '+049.890.9876' union all
select '0x0x0x' union all
select 'Work: (444) 123-4567'


--usage of above function
select strn, dbo.fn_numeric(strn) 'numeric'
from t
where isnumeric (dbo.fn_numeric (strn)) = 1

--
Vishal Parkar
vgparkar@yahoo.co.in

[quoted text, click to view]