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:

 

000123

001234

012345

 

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=

 case

  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

 end

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 04, 2004 2:43 PM by khurram
Filed under:

Comments

No Comments

New Comments to this post are disabled