Convert nvarchar to integer in SQL

Question

I have a table where the version value's are nvarchar rather than integer so when I use a greater than statement it isn't pulling back the correct data

I am trying to convert the version to integer but am getting an error, please help

Declare @dversion   int;

set @dversion = (select convert(int, displayversion) from Inv_AddRemoveProgram where DisplayName like 'Symantec Enterprise Vault%')

select distinct v1.name, v1.[user], t1.displayname, @dversion from vComputer v1

inner join Inv_AddRemoveProgram t1
on v1.Guid = t1._ResourceGuid

where t1.DisplayName like 'Symantec Enterprise Vault%'

and @dversion > '10.0.17573'

Error received: Msg 245, Level 16, State 1, Line 3 Conversion failed when converting the nvarchar value '10.0.17573' to data type int.

I also tried writing two simple queries to convert the version value in that table which also didn't work

select displayname, CONVERT(INT, displayversion) from Inv_AddRemoveProgram

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

select displayname, CAST(displayversion AS INT) from Inv_AddRemoveProgram

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

well the error message has the clue in it, `10.0.17573` is not an integer value and you cannot convert this value to integer. — M.Ali, May 22 '15 at 14:58
You need to remove the `.` from the string. You may need to split them into `Major`, `Minor`, `Micro` ? — Christian Phillips, May 22 '15 at 14:58
`3.1.05160` Is not an integer or a number type of any kind because there are more than 1 periods `.` If you want to display each number entry in the string as an int then you will have to split the string up by the periods then convert each entry to ints. Or use @christiandev's suggestion and remove all the periods. — John Odom, May 22 '15 at 14:59
@ m.ali 10.0.17573 is not a numerical value , more exactly. 10.175 is not an integer but would be converted. — P. O., May 22 '15 at 14:59

score 0 · Answer 1 · answered May 22 '15 at 15:02

It looks like your issue may be that you are trying to convert a float to an int. Or, actually, I'm not sure what kind of number '3.1.05160' might be. Probably not even a float with two decimals in it.

If you want to arrange on a field like that, I think you'd need to split that number into 3 parts and convert each of them into numbers and THEN order on them?

score 0 · Answer 2 · answered May 22 '15 at 15:14

0

You need int in where clausule. Try this:

and @dversion > CAST(replace('10.0.17573', '.', '') as int)

answered May 22 '15 at 15:14

Arkadiusz

489
2
10

score 0 · Answer 3 · answered May 22 '15 at 15:19

Try this:

DECLARE @t TABLE ( v NVARCHAR(20) )
INSERT  INTO @t
VALUES  ( '10.0.17573' ),
        ( '10.1.17573' ),
        ( '10.0.1753' ),
        ( '12.3.17573' ),
        ( '11.0.17573' ),
        ( '9.0.17573' ),
        ( '9.20.17573' )

SELECT  *
FROM    @t
WHERE   CAST(PARSENAME(v, 3) AS INT) > CAST(PARSENAME('10.0.17573', 3) AS INT)
        OR ( CAST(PARSENAME(v, 3) AS INT) >= CAST(PARSENAME('10.0.17573', 3) AS INT)
             AND CAST(PARSENAME(v, 2) AS INT) > CAST(PARSENAME('10.0.17573', 2) AS INT))
        OR ( CAST(PARSENAME(v, 3) AS INT) >= CAST(PARSENAME('10.0.17573', 3) AS INT)
             AND CAST(PARSENAME(v, 2) AS INT) >= CAST(PARSENAME('10.0.17573',2) AS INT)
             AND CAST(PARSENAME(v, 1) AS INT) > CAST(PARSENAME('10.0.17573',1) AS INT))

Output:

Convert nvarchar to integer in SQL

3 Answers3