2

I need to compare two columns in a sql table. The data in one column has a leading single quote and may have leading zeros and the other column may have leading zeros. I need to trim the leading zeros off one column and the leading quote and leading zeros on the other before I do the comparison. Is this Possible?

    F.RoutingNum
    123456
    234356
    014233
    233455


    T.RoutingNum
    '123456
    '234356
    '014233
    '0233455

I tried this but it does not trim to leading zeros off the T.RoutingNum column.

CASE 
    WHEN Replace(Ltrim(Replace(F.RoutingNum, '0', ' ')), ' ', '0') 
        <> SUBSTRING(Replace(Ltrim(Replace(T.RoutingNum, '0', ' ')), ' ', '0'), 2, 20) 
        THEN 'DO not match' 
    ELSE 'match'
END
user973671
  • 1,620
  • 6
  • 27
  • 39

2 Answers2

2

This is a good write-up on how to remove leading zeroes depending on your data type:
http://www.sql-server-helper.com/functions/trim-leading-zeros.aspx

If your RoutingNum is actually numeric it's as easy as casting it to an INT

Here is a related question which probably also answers your problem:
Removing leading zeroes from a field in a SQL statement

EDIT: And, I just confirmed that these same results are easily determined using this method:
http://bit.ly/vQYVal

Community
  • 1
  • 1
Matthew
  • 10,244
  • 5
  • 49
  • 104
0
case
    when cast(isnull(f.routingnum, 0) as bigint) 
        <> cast(isnull(replace(t.routingnum, '''', ''), 0) as bigint)
        then 'do not match'
    else
        'match'
end
  • I get this error, The conversion of the nvarchar value '3114970664' overflowed an int column. Is there a way around this? Thanks – user973671 Nov 29 '11 at 19:35
  • @user973671 use `bigint` or `decimal`. What's the largest value you will see?? –  Nov 29 '11 at 19:36
  • cast(F.RoutingNum as decimal) works but cast(replace(T.RoutingNum, '''', '') as decimal) gives the error, Error converting data type nvarchar to numeric. I am guessing the single quote is not being removed before it is getting cast. Any suggestions? thanks – user973671 Nov 29 '11 at 19:55
  • @user973671 see my edit. Try using `bigint`. Also, with `decimal`, specify the precision and scale. I.e. `cast(replace(t.routingnum, '''', '') as decimal(20, 0)` or something to that effect. Make sure your `replace` is using only single quotes. The second parameter is 4 single quotes, and the third param is 2 single quotes. –  Nov 29 '11 at 19:59
  • There is also some null values in the T.RoutingNum column, maybe that is why it is giving me that error. – user973671 Nov 29 '11 at 20:01
  • That did not seem to work. I will keep working on it Thanks for all you help. – user973671 Nov 29 '11 at 20:47