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?
ASKER
Ok. How do I use that? When I want to apply it to the field in question?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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')
geez... not even a split :(
surprised also !
but sometimes: KISS
anyhow, glad we could help.
but sometimes: KISS
anyhow, glad we could help.
Open in new window