Welcome to weblogs.com.pk Sign in | Join | Help

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)

  else pincode


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?

Published Friday, June 4, 2004 2:43 PM by khurram
Filed under:


No Comments

New Comments to this post are disabled