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
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