How to trim leading single quote and leading zeros from sql query

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

Answers


case
    when cast(isnull(f.routingnum, 0) as bigint) 
        <> cast(isnull(replace(t.routingnum, '''', ''), 0) as bigint)
        then 'do not match'
    else
        'match'
end

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


Need Your Help

Can't create a new OpenSSL key

php openssl

I want to learn OpenSSL but I'm just getting boolean false when I am trying to use this code:

Linux Kernel Programming, how to get information?

linux linux-kernel

I'm a newbie on Linux programming and I don't know exactly where I can get information of functions for example. There is a directory Documentations in my sources. But I can't find semaphore stuff ...

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.