Hi there - I would like to share this strip of code with our SQL 2000
DBA community. The code below strips all non-numeric characters from a
given string field and rebuilds the string. Very simple, but I had to
build it from scratch due the lack of info on this specific matter. I
am sure there are better solutions out there, although I will be glad
if this script can help anyone. Feel free to modify and comment it
back.
Regards,
Rubem Linn Junior
MCSE, .NET developer
Web Apps Specialist
------------------------------------------------------- BEGIN
---------------------------------------------------
DECLARE @String_Length AS INTEGER -- Length of the given string
DECLARE @Original_String as NVARCHAR(50) -- The field to strip
non-numeric chars
DECLARE @counter as integer -- simple counter variable
DECLARE @Stripped_String as nvarchar(50) -- The field after been
stripped
-- Get the length of the field (string) to be parsed
SELECT @String_Length = len(someStringField) FROM SomeTable WHERE
FilterID = 001
-- Get the field (string) to be parsed
SELECT @Original_String = someStringField FROM SomeTable WHERE
FilterID = 001
-- Set counter variable to 1
SELECT @counter = 1
-- Reset this variable
SELECT @Stripped_String = ''
-- Initiate loop from 1 to the Length of the given string
WHILE (@counter) <= @String_Length
BEGIN
-- Check if the char in the lap is numeric
if substring(@Original_String,@counter,1) LIKE '[0-9]'
BEGIN
-- Load this variable with the non-numeric
-- data stripped from the original string
select @Stripped_String = @Stripped_String +
substring(@Original_String,@counter,1)
END
-- Increment the counter by one
select @counter = @counter + 1
END
-- Print the original string with all characters
PRINT @Original_String
-- Print the numeric data that was stripped out
PRINT RTRIM(LTRIM(@Stripped_String))