Link to home
Start Free TrialLog in
Avatar of n00b0101
n00b0101

asked on

Replace non-numeric characters in MSSQL field?

Is there a way to check if a field in my MSSQL database has any non-numeric characters in it, via query?
Avatar of BrandonGalderisi
BrandonGalderisi
Flag of United States of America image

Here you go.  isnumeric() isn't perfect in that regards.  This will return 1 if it contains ALL numeric, so you want to test for 0 to see if it contains any NON-numeric.
if object_id('dbo.fn_isNumeric') is not null
   drop function dbo.fn_isNumeric 
go
create function dbo.fn_isNumeric (@Input nvarchar(max))
returns bit
as
begin
return (select case when 
          patindex('%[^0-9]%',@input)=0 then 1 else 0 end
     )
end
go
select dbo.fn_isNumeric ('1234xasdf66')
select dbo.fn_isNumeric ('123466')
~

Open in new window

Avatar of n00b0101
n00b0101

ASKER

Ok.  How do I use that?  When I want to apply it to the field in question?
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This will strip non-numeric characters from a field.
if object_id('dbo.fn_StripNonNumeric') is not null
   drop function dbo.fn_StripNonNumeric 
go
create function dbo.fn_StripNonNumeric (@Input nvarchar(max))
returns nvarchar(max)
as
begin
     while patindex('%[^0-9]%',@input)>0
          set @input = substring(@input,1,patindex('%[^0-9]%',@input)-1) +  substring(@input,patindex('%[^0-9]%',@input)+1, (datalength(@input)/2))
return @input
end
go
select dbo.fn_StripNonNumeric ('1234xasdf66')

Open in new window

geez... not even a split :(
surprised also !
but sometimes: KISS

anyhow, glad we could help.