TSql: Dropping leading zeroes
At my job, a weird mistake happened few days back. We changed the template of the PIN codes that are randomly generated. A little tricky algorithm involved ends up concatenating multiple numbers to make a single value. In case the values being concatenated have less width, by mistake, zeroes were prefixed. We ended up with numbers containing values something like:
These values were mailed to a third party in the Excel format. The Excel was smart enough, it removed the leading zeroes, and wrong values got printed causing lot of problems at our support desks L
I was required to change all such values in the database removing leading zeroes. I came up with this solution:
update users set pincode=
when pincode like '000%' then substring(pincode, 4, len(pincode)-3)
when pincode like '00%' then substring(pincode, 3, len(pincode)-2)
when pincode like '0%' then substring(pincode, 2, len(pincode)-1)
where usertype='card' and userid like 'buzz0%' and pincode like '0%'
I assumed in this solution (I verified earlier by a query) that there are no more than three leading zeroes in the pincode field. Do you have any better approach?