SQL Different column type comparison error

Question

Hi I have a stored procedure which suppose to compare between 2 columns on different tables

Users.ID => int
Trans.ID => nvarchar

Sometimes the value in Trans.ID is not numeric at all, and sometimes it's null, which causes an error while trying to compare

is there a way to try to parse Trans.ID into a number and in case it doesn't succeed to return 0??

I tried NullIf() but it doesn't work when the value inside is not numeric.

which version of Database are you using? – rs. Oct 01 '12 at 13:59 — rs., Oct 01 '12 at 13:59
mssql, thanks for quick ans :) – user1326293 Oct 01 '12 at 14:00 — user1326293, Oct 01 '12 at 14:00

score 3 · Answer 1 · answered Oct 01 '12 at 13:59

3

Assuming Trans.ID is a varchar(20) field, you can convert the Users.ID field to a varchar, use COALESCE to handle NULL values, and compare as follows:

WHERE CAST(Users.ID AS varchar(20)) = COALESCE(Trans.ID, '')

answered Oct 01 '12 at 13:59

LittleBobbyTables - Au Revoir

32,008
25
109
114

score 3 · Answer 2 · answered Oct 01 '12 at 14:00

3

If using sql server you can do this

CASE WHEN ISNUMERIC(Trans.ID) = 0  then null 
else cast(Trans.ID as int) end = Users.ID

answered Oct 01 '12 at 14:00

rs.

26,707
12
68
90

score 1 · Accepted Answer · answered Oct 01 '12 at 14:00

1

you can do something like:

select * from users u 
inner join trans t on u.userid  = (CASE WHEN ISNUMERIC(t.id) = 1 THEN CONVERT(int, t.id) ELSE 0 END)

hope this helps.

answered Oct 01 '12 at 14:00

Losbear

3,255
1
32
28

score 0 · Answer 4 · answered Oct 01 '12 at 14:00

0

If it is just equality comparison (which I think it is to make sense), I would convert Users.ID to NVARCHAR and then compare with Trans.ID.

answered Oct 01 '12 at 14:00

Vikdor

23,934
10
61
84

score 0 · Answer 5 · edited May 23 '17 at 12:27

IsNumeric() is not always right, e.g. ' - ' and '.' both return 1 for IsNumeric, but will fail your query.

This function (Adapted from here)

create function dbo.GetNumeric(@x varchar(10)) returns float as
begin
return
    case
    when @x is null or @x = '' then null -- blanks
    when @x like '%[^0-9e.+-]%' then null -- non valid char found
    when @x like 'e%' or @x like '%e%[e.]%' then null -- e cannot be first, and cannot be followed by e/.
    when @x like '%e%_%[+-]%' then null -- nothing must come between e and +/-
    when @x='.' or @x like '%.%.%' then null -- no more than one decimal, and not the decimal alone
    when @x like '%[^e][+-]%' then null -- no more than one of either +/-, and it must be at the start
    when @x like '%[+-]%[+-]%' and not @x like '%[+-]%e[+-]%' then null
    else convert(float,@x)
    end
end

Your query (involving inequality)

where users.id >= case when IsNull(dbo.GetNumeric(trans.id),0)

And if trans.id does not involve decimal points

where user.id >= case when trans.id not like '%[^0-9]%' and trans.id >''
                      then trans.id end

Of course, if it's a simple equality, just cast the int to a varchar

where right(users.id,10) = trans.id

SQL Different column type comparison error

5 Answers5

Linked