Replacing data in a string

Hi

I have a table with unique data in but it has been inputted, below is an example of the data

6331245073
638 535 0797
7023593578
ID Number 650 379 4428
OCA8 9F 32
ocb92w153
vh235704

I need to strip out the all letters and spaces so it will look like this:

6331245073
6385350797
7023593578
6503794428

ect....

Thanks

Rich

Hi Rich,

You need to create a scalar function to do this. Here is an example:
CREATE FUNCTION FormatString(@.input VARCHAR(100))
RETURNS VARCHAR(100)
AS
BEGIN
DECLARE
@.character CHAR(1),
@.newstring VARCHAR(100),
@.counter INT

SET @.counter = 0
SET @.newstring = ''

WHILE @.counter < LEN(@.input)
BEGIN
SET @.counter = @.counter + 1
SET @.character = SUBSTRING(@.input, @.counter, 1)

IF @.character >= '0' AND @.character <= '9'
SET @.newstring = @.newstring + @.character
END

RETURN @.newstring
END

You can call the function as follow (note the dbo. prefix):

SELECT dbo.FormatString('ID Number 650 379 4428' )
SELECT dbo.FormatString(fieldname) FROM tablename

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Thanks Geert did the job

Rich