how to replace character from a mobile and phone number

_{Tuesday, October 25, 2011 8:25 AM}

I have mobile and phone number in my ODS's

i.e (464) 617-2704 in this format but I want to remove character i.e (( ,) and - ) from it and want to convert it into double data type

Tahir Ayoub

_{Tuesday, October 25, 2011 1:48 PM ✅Answered | 1 vote}

Hi, just another way using REPLACE instead of SUBSTRING. Plus, it will remove the characters ( ) - and Blank "dynamically". give it a try.

REPLACE(REPLACE(REPLACE( REPLACE( [ColumnName] , "(", "") , "-", ""), ")", ""), " ", "")

only change ColumnName.

B. Regards.

_{Tuesday, October 25, 2011 10:01 AM | 1 vote}

Hi Tahir,

you could use a script component in SSIS and use Regular Expression to extract the number and then convert it to double.

Or like that :

string phonenr = "(464) 617-2704";

phonenr = phonenr.Replace("(","");

phonenr = phonenr.Replace(") ","");

phonenr = phonenr.Replace("-","");

Hope it helps

Regards

Régis

_{Tuesday, October 25, 2011 10:13 AM | 1 vote}

Hi Tahir,

You can use derived column and build an expression using sub string keyword.

something like this...

SUBSTRING(ColumnName,2,4)+SUBSTRING(ColumnName,6,8)+SUBSTRING(ColumnName,10,13)

Didnt test it but still ...u can try something like this...

Mark as answer if the post help you... Regards, Indraneel A

Additional resources