Not able to cast nvchar as number

Question

Not able to cast nvchar as number

Christopher Jack 1,616

Hi,

We have had some SQL thats been running for about a year and it has suddenly stopped working.

CAST(ISNULL(M.DEFINED_KEY1, 0) AS NUMERIC(9, 0)) AS [No_of_Cats]

It basically puts a 0 instead of null and then makes it numeric.

I have checked the column and all it has in it is nulls or integers.

Anyone got any idea why this would suddenly stop working?

The error im getting is

Error converting data type nvarchar to numeric.

The largest number in the field is 1100000, the smallest is 0 and Null is the only other type of value.

Yitzhak Khabinsky 26,586 Reputation points

2021-01-22T15:48:16.68+00:00

Please edit your question and add few M.DEFINED_KEY1 values that are failing.
Otherwise, it is almost impossible to help you.

Accepted answer

3 additional answers

Your answer

Yitzhak Khabinsky 26,586 Reputation points

2021-01-22T15:48:16.68+00:00

Please edit your question and add few M.DEFINED_KEY1 values that are failing.
Otherwise, it is almost impossible to help you.

Answer 1

"I have checked the column and all it has in it is nulls or integers."

You might have checked the data visually, in that case you have garbage in the data which doesn't show up visually.

The trick is to find those rows, and those rows only. Below show you how you can do that. In your WHERE clause, use IS NOT NULL so you don't return the rows which are NULL and also use TRY_CAST to find those that aren't convertible to int:

DROP TABLE IF EXISTS #t

CREATE TABLE #t(c1 int identity, c2 nvarchar(10))

INSERT INTO #t(c2) VALUES ('1')
INSERT INTO #t(c2) VALUES ('123')
INSERT INTO #t(c2) VALUES (NULL)
INSERT INTO #t(c2) VALUES ('12' + CHAR(9)) --Note that CHAR(9) is TAB

--It looks like data is fine!
SELECT * FROM #t

--But conversion fails because of TAB
SELECT c1, CAST(c2 AS int)
FROM #t

--How to find the failed rows, and those only?
SELECT c1, c2
FROM #t
WHERE c2 IS NOT NULL
AND TRY_CAST(c2 AS int) IS NULL

Christopher Jack 1,616 Reputation points

2021-01-22T16:43:24.173+00:00

Thanks to everyone, managed to identify the problem fields.

Answer 2

Guoxiong 8,206

Make sure there is no empty string in the data. For example, the following CAST would fail:

DECLARE @s nvarchar(20) = '';
SELECT CAST(ISNULL(@s, 0) AS NUMERIC(9, 0));

Msg 8114, Level 16, State 5, Line 2
Error converting data type nvarchar to numeric.

It is better to do this:

DECLARE @s nvarchar(20) = '';
SELECT CAST(ISNULL(NULLIF(@s, ''), 0) AS NUMERIC(9, 0));

Christopher Jack 1,616 Reputation points

2021-01-22T15:43:50.227+00:00

Nope still failing :(
Guoxiong 8,206 Reputation points

2021-01-22T16:04:21.083+00:00

Try to use TRY_CAST. If that column has some characters in it, it will return NULL.
Christopher Jack 1,616 Reputation points

2021-01-22T16:15:36.077+00:00

The query ruins with fine without any errors when I change it to that.
Guoxiong 8,206 Reputation points

2021-01-22T16:21:59.603+00:00
If you really want 0 instead of NULL in case there are some characters in the column, try this:

DECLARE @s nvarchar(20) = 's'; SELECT CAST(ISNULL(TRY_CAST(ISNULL(@s, 0) AS NUMERIC(9, 0)), 0) AS NUMERIC(9, 0));
Viorel 122.6K Reputation points

2021-01-22T16:35:47.423+00:00
What results do you get in this case?

select * from MyTable where DEFINED_KEY1 is not null and try_CAST(DEFINED_KEY1 AS NUMERIC(9, 0)) is null

Show the details.

Answer 3

Olaf Helper 47,441

I have checked the column and all it has in it is nulls or integers.

But the error messages says, the column is of type nvarchar. I guess, there is an "invisible" character in the data, like control chars (tab, CR, LF), which makes the implicit (!) conversion failing.
You can try using the ISNUMERIC (Transact-SQL) function to find the faulty data.

Christopher Jack 1,616 Reputation points

2021-01-22T16:06:25.587+00:00

Thanks Olaf, I tried your suggestion but it is only saying Nulls are non numeric ?

Answer 4

I would suggest the following approach.
Change CAST(...) to TRY_CAST(...)

It will allow you to filter just questionable rows for analysis. After that you will be able to adjust the conversion/casting statement.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, DEFINED_KEY1 NVARCHAR(100) NULL);
INSERT INTO @tbl (DEFINED_KEY1) VALUES
(N''),
(NULL),
(N'1100000'),
(N'0');
-- DDL and sample data population, end
-- all rows
SELECT * 
 , TRY_CAST(ISNULL(M.DEFINED_KEY1, 0) AS NUMERIC(9, 0)) AS [No_of_Cats]
FROM @tbl AS m;
-- failing to convert
;WITH rs AS
(
 SELECT * 
 , TRY_CAST(ISNULL(M.DEFINED_KEY1, 0) AS NUMERIC(9, 0)) AS [No_of_Cats]
 FROM @tbl AS m
)
SELECT * FROM rs
WHERE [No_of_Cats] IS NULL;

Share via

Not able to cast nvchar as number

3 additional answers

Your answer