Removing Non-numeric characters from Alpha-numeric string

Mandip

Hi,

I have one column in which i have Alpha-numeric data like

COLUMN X

-----------------------

+91 (876) 098 6789

1-567-987-7655

.

.

.

.

so on.

I want to remove Non-numeric characters from above (space,'(',')',+,........)

i want to write something generic (suppose some function to which i pass the column)

thanks in advance,

Mandip

FlorianReischl

Hello Mandip

Its not very fast, but works:

Code Block

DECLARE @phone VARCHAR(50)

SET @phone = '+91 (876) 098 6789'

DECLARE @pos INT

SET @pos = 0

WHILE (@pos < LEN(@phone))

BEGIN

DECLARE @c CHAR(1)

SET @c = SUBSTRING(@phone, @pos, 1)

IF (@c NOT BETWEEN '0' AND '9')

SET @phone = REPLACE(@phone, @c, '')

SET @pos = @pos + 1

END

PRINT @phone

Regards

Flo

Mandip

Thanks Flo,

Actually I am already aware of this....

.... Just think I have to write an function which can be used multiple times for millions of rows...it will be too slow in that case

So if you have anyother option. most welcome.

Frank Kalis

Mandip wrote:

Thanks Flo, Actually I am already aware of this.... .... Just think I have to write an function which can be used multiple times for millions of rows... So if you have anyother option. most welcome.

Here's a function:

CREATE FUNCTION dbo.RemoveChars(@Input varchar(1000))
RETURNS VARCHAR(1000)
BEGIN
DECLARE @pos INT
SET @Pos = PATINDEX('%[^0-9]%',@Input)
WHILE @Pos > 0
BEGIN
SET @Input = STUFF(@Input,@pos,1,'')
SET @Pos = PATINDEX('%[^0-9]%',@Input)
END
RETURN @Input
END
GO

...but I doubt that it is fun running this against a table with millions of rows. You should probably consider exporting the data, do the scrubbing with a scripting language and import back in.

Mandip

Thanks frank,

Yeah you are right about fun running it... I am thinking to convert this function to .Net CLR function. I know thats more performance efficient... But before I develop that I need some temporary solution. Thanks for providing that

Mandip..

Michael B in OKC

The function provided works great with one exception, it removes decimal characters. I've tried altering the RegEx in the PATINDEX to include decimals but am having problems getting it to work.

PATINDEX('%[^0-9\.]%',@Input)

Shouldn't adding the "\." to the RegEx set provide the required result

Michael