'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.

sqlsql-serverintegernvarchar


Solution 1:[1]

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.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 QPeiran

Related Questions