Converting nvarchar to int, converting phone with symbols with only numbers-CodePudding

I am trying to convert phone number from the column 'phone' from the table 'Clients'. I have tried the following syntaxes, but I still get error messages -

1. SELECT CAST(phone as int)
   FROM Clients

Error: Conversion failed when converting the nvarchar value '030-3456789' to data type int

2. SELECT CONVERT(int, phone)
   FROM Clients

Conversion failed when converting the nvarchar value '030-3456789' to data type int.

3. SELECT CAST(phone AS BIGINT)
   FROM Clients
   WHERE ISNUMERIC(phone) = 1

The query doesn't return error but there is no result, the column is empty.

CodePudding user response：

It looks (from your example syntax) like you might be using SQL Server.

If that's the case and it's 2017 you can do the following which copes with any combination of non-numeric values.

Based on your comments the following should work

select Try_Convert(bigint, Replace(Translate('(5) 789-0123','()-','   '),' ',''))

Result: 57890123

If you are using SQL Server 2016 or earlier you have to nest multiple replacements:

select Try_Convert(bigint, Replace(Replace(Replace(Replace('(5) 789-0123)','-',''),'(',''),')',''),' ',''))

CodePudding user response：

Because at least some of your records cannot be covert to numeric by default, as the indicated one 030-3456789 You basically need to replace/eliminate the dash in between:

SELECT cast(replace('12-3', '-', '') as int)

Anyway, welcome to StackOverflow.