Error on converting String to Integer

Transact-SQL
1

I am retrieving a number portion from a string and convert it to integer.

CAST(LTRIM(RIGHT(ITEMNAME,2)) as int)

Although that portion is definitely a number that could be cast into an Integer I get constantly the error message:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the nvarchar value 'en' to data type int.

There are about 13'000 records returned and I had been checking for an hour or so that every extracted string portion really is a number and that the string length always reflects the amount of digit of that number. But the error would persist!

Then placing a TOP 20000 in front -with only 13000 records in the stack!!!- the conversion works without throwing that error.

This doesn't make any sense to me.

Someone can explain

PS. Beside the fact that the value 'en' does not even exist in LTRIM(RIGHT(ITEMNAME,2))

2

what version of sql server are you on. you could try
TRY_CONVERT

1 Like
3

SQL Server 2012

My query is now working using a TOP 100 limitation so I don't need another solution.
It would just be nice to understand the logic behind that strange behavior.

BUT... using TRY_CONVERT actually works and makes the TOP 100 unnecessary! Good!

Martin

4

Weird.

You could check directly for row(s) with nondigits in those positions like so:
WHERE RIGHT(ITEMNAME,2) LIKE '%[^0-9]%'

Btw, you don't need the LTRIM, because leading space(s) won't prevent a conversion to int anyway.

5

Yes, very weird. Just imagine:
1 Query:

select  NUMBER, CAST(NUMBER as int) from (
select CASE WHEN LEFT(ITEMNAME,1) = 'H' THEN NULL ELSE CAST(LTRIM(RIGHT(ITEMNAME,2)) as nvarchar) END as NUMBER
from Table)x

=> returns: Conversion failed when converting the nvarchar value 'en' to data type int.

2.Query

select  NUMBER, CAST(NUMBER as int) from (
select CASE WHEN LEFT(ITEMNAME,1) = 'H' THEN NULL ELSE CAST(LTRIM(RIGHT(ITEMNAME,2)) as nvarchar) END as NUMBER
from Table)x
WHERE NUMBER not in ('en','LE',',4','DA')

=>returns all records, every number is displayed as Integer

3.Query

select  NUMBER from (
select CASE WHEN LEFT(ITEMNAME,1) = 'H' THEN NULL ELSE CAST(LTRIM(RIGHT(ITEMNAME,2)) as nvarchar) END as NUMBER
from Table)x
WHERE NUMBER in ('en','LE',',4','DA')

=> NO record is returned

6

Again, why not just return the "RIGHT(ITEMNAME, 2)" so you can see what it is, instead of just going by comparisons against it?

SELECT TOP (100) ITEMNAME, RIGHT(ITEMNAME, 2)
FROM ...
WHERE RIGHT(ITEMNAME,2) LIKE '%[^0-9]%'

7 
Select CAST(RIGHT(ITEMNAME,2) as INT)
From Table
WHERE RIGHT(ITEMNAME,2) LIKE '%[0-9]%'

=>Conversion failed when converting the nvarchar value '1)' to data type int.
The same thing happens if using '%[^0-9]%'. Sounds very illogical, I know. ITEMNAME is the result of a multiple replace operation, can this has some effect on it?

RTRIM(LTRIM(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(INVENTTABLE.ITEMNAME,'mm.',''),'barra',''),'; 3.1',''),'(bloq)',''),'mm',''),' m',''))) as ITEMNAME
8

Yeah, something else is going on, and it's all hidden from us. Only you know, you're obviously not going to give us any details or actual code, so I give up; good luck with it.

9

sounds like you are missing a replace of one or more characters to replace including )

10

There's an ISNUMERIC function in SQL Server that I often find helpful in situations like this.

11

Hi instead of converting to INT try to convert as FLOAT
Cheers