Right function + conversion

Question

I'm comparing columns from two tables. However, one of them contains two-value numbers (like 01), while the other - one-value numbers (like 1).

As a result, the following condition doesn't work: `WHERE column_1 <> column_2 (results '01' and '1' are considered not equal).

I was told to add a zero to every number of the second column to the results and tried to use this: column_1 <> RIGHT(column_2('0' + CONVERT(VARCHAR(2), '1'), 2)) but the error message I get from SQL management Studio is that "right function requires two arguments".

Does anyone have the idea what can be done? Thanks!

possible duplicate of [Better techniques for trimming leading zeros in SQL Server?](http://stackoverflow.com/questions/662383/better-techniques-for-trimming-leading-zeros-in-sql-server) — dani herrera, Sep 17 '14 at 08:01

score 1 · Answer 1 · answered Sep 17 '14 at 08:02

1

You should cast the string to integers (CAST AND CONVERT) and compare the integer number:

where CAST(column_1 as INT) <> CAST(column_2 as INT)

answered Sep 17 '14 at 08:02

JotaBe

38,030
8
98
117

+1, only that `column_1` appears to be already integer in the OP's question. Cast shouldn't be needed. – mabi Sep 17 '14 at 08:05
I thought of the covert, but had difficulties with the syntax. If that be possible, could you please show how such a statement should look? Thanks! – Yan Sep 17 '14 at 08:14
??? It's on the answer: `where CAST(column_1 as INT) <> CAST(column_2 as INT)`. Besides the "CAST AND CONVERT" part is a link to the MSDN docs which have a deep explanation and plenty of examples. – JotaBe Sep 17 '14 at 08:16

score 0 · Answer 2 · answered Sep 17 '14 at 08:18

As @JotaBe mentioned, you should compare integers, not stirngs. But still, your code in second case isn't syntactically correct, it should be:

column_1 <> RIGHT('0' + CONVERT(VARCHAR(2), column_2), 2)

But it won't work with negative numbers, for example.

Right function + conversion

2 Answers2