Search code examples
sqlformatnvarchar

SQL nvarchar Formatting

So i have a Datatype nvarchar which is a Phone number its currently in the format of (123)456-7891 but i want it to be 1234567891 no Brackets or any thing. Some of the Numbers have words in front like ABC(123)456-7891 << This format so its a nvarchar that allows number and Letter but i want the Format of the number to be without any Special words ()-- how can i convert it i have tried.

FORMAT(cast( PhoneNumber as numeric),'##########') as PhoneNumber

It saves the Procedure properly but when i try to excute it it says Error converting data type nvarchar to numeric i tried only FORMAT(PhoneNumber,'##########), but it says Argument data type nvarchar is invalid for argument 1 of format function. I cant get it to work please let me know if you know any thing about it.

Solution

  • Just use replace():

    select replace(replace(replace(PhoneNumber, '-', ''), ')', ''), '(', '')

    You cannot convert a string that contains '('s, ')'s, and '-'s to a number.