Sql Query to Convert Nvarchar to Int

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

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

Sql query to convert nvarchar to int

3600.00 is not integer so CAST via float first

sum(CAST(CAST(amount AS float) AS INT))

Edit:

Why float?

• no idea of precision or scale across all rows: float is the lesser evil perhaps
• empty string will cast to zero for float, fails on decimal
• float accepts stuff like 5E-02, fails on decimal

Converting NVARCHAR to INT datatype in SQL Server 2008

Here is another logic that will convert the column to INT if it's possible, similar to TRY_CONVERT in 2012 onward.

The case condition checks to see when your column IS NOT LIKE any non-digit. It's a short way of checking if your column IS LIKE any character or special character. If there is anything other than a digit in that column, the conversion isn't done. If it's only digits (which is what we need for an INT conversion) then the conversion is done.

SELECT
    IdField
    ,DataField
    ,CASE WHEN DataField NOT LIKE '%[^0-9]%' THEN CONVERT(int, DataField) END New_Column
FROM #SampleData

Convert nvarchar to numeric in MSSQL Server

Your data would appear to have values are not valid numeric values. Use try_convert():

select try_convert(numeric(38, 12), col)

This will return NULL if there is a failure in the conversion.

You can find the values that fail the conversion by doing:

select col
from t
where try_convert(numeric(38, 12), col) is null and col is not null;

You need to use try_convert() wherever you reference the column as a numeric. Converting in the select only applies to the select.

How to cast nvarchar to integer?

From MSDN:

Syntax for CAST:
CAST ( expression AS data_type [ ( length ) ] )

Your solution should look something like this:

SELECT c.CityName, CAST(c.Population AS INT) AS SumOfPopulation
FROM dbo_City AS c
WHERE ISNUMERIC(c.Population) = 1 AND CAST(c.Population AS INT) BETWEEN 189999 AND 200000
ORDER BY c.CityName, CAST(c.Population AS INT)

You shouldn't need the sum function unless you want to know the total population of the table, which would be more useful if it was a table of countries, cities, and city populations, unless this particular city table is broken down further (such as with individual zip codes?). In that case, the below would be the preference:

SELECT c.CityName, SUM(CAST(c.Population AS INT)) AS SumOfPopulation
FROM dbo_City AS c
WHERE ISNUMERIC(c.Population) = 1
GROUP BY c.CityName
HAVING SUM(CAST(c.Population AS INT)) BETWEEN 189999 AND 200000
ORDER BY c.CityName, SUM(CAST(c.Population AS INT))

I hope this helps point you in the right direction.

-C§

Edit: Integrated the "fail safe" from your linked syntax, which should stop that error coming up. It adds a filter to the column to only process those that are able to be cast to a numeric type without extra processing (such as removing the comma as in vkp's response).

---

Related Topics