CODEWITHSUNDEEP
sqlsql-servert-sqlssms
Galeaettu
Galeaettu

Reputation: 74

Converting nvarchar to int, isnumeric returning 0 on a number

I have a temporary table with datatype NVARCHAR which I need to migrate to a table with datatype INTEGER.

I have two NVARCHAR tables which need to be INTEGER, one is working and the other is giving an error,

Conversion failed when converting the nvarchar value '9824
' to data type int.

I tried removing that extra space using LTRIM and RTRIM but ISNUMERIC() is still giving 0 and the error stays the same.

This is the code resulting in an error,

SELECT CAST(table_name AS integer)
FROM OLTP.table

What I've tried:

SELECT ISNUMERIC(REPLACE(Revenue, ' ', '')), Revenue, ISNUMERIC(REPLACE(LTRIM(RTRIM(Revenue)), '  ', ' '))
FROM OLTP.ExtractedData

Sample Data

(No column name)    Revenue (No column name)
0   9824    0
0   5661    0
0   3250    0
0   2959    0
0   2511    0
0   2072    0
0   2056    0
0   1705    0
0   1704    0

Upvotes: 1

Views: 464

Answers (4)

Sumit raj
Sumit raj

Reputation: 831

  SELECT  PARSE(column_name AS int )  from table

Tested

Upvotes: 0

SQLChao
SQLChao

Reputation: 7847

I would check the length. This will give you an idea if how many special characters you are looking for.

SELECT LEN(Revenue)
FROM OLTP.ExtractedData

Then use SUBSTRING and ASCII to find out what the character is.

SELECT 
  ASCII(SUBSTRING(Revenue, 5, 1))
FROM OLTP.ExtractedData

Upvotes: 0

Chris Steele
Chris Steele

Reputation: 1381

You probably have a linebreak in there, TRIM/RTRIM don't trim those.

declare @value nvarchar(10) = '1234
'

select ISNUMERIC(Replace(CHAR(9), '', @value))
select ISNUMERIC(Replace(CHAR(10), '', @value))
select ISNUMERIC(Replace(CHAR(13), '', @value))
select ISNUMERIC(Replace(CHAR(32), '', @value))

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270843

Instead of cast() use `try_cast()

SELECT TRY_CAST(table_name AS integer)
FROM OLTP.table

Upvotes: 0

Related Questions