[SOLVED] Converting nvarchar to int, converting phone with symbols with only numbers

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

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.

Solution

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)','-',''),'(',''),')',''),' ',''))