Friday, June 11, 2004

Padding with 0's infront, to make same length digits (using SQL)

Lets say you have a task to format some digits using SQL so that it has same length for eg: 1,2 23, 346 should chage to : 001, 002, 023, 346 so that all are 3 digit numbers after paddind with 0's infront. It seems so easy to me in C# but when i was asked to do it using SQL statements, i was a bit unsure. so, here is what i discovered.if you are using :
1) MS Access try this
2) MS SQL Server try this
3) IMB DB2 the keyword you've gotta look up is DIGITS (then use SUBSTR).
What DIGITS does is that it returns a character string representation of its argument. you can use DIGITS to given you a string something like this '000000002'(assuming current number we are working on is 2)
Then you have to use SUBSTR to pull out the last 3 digits(if you are trying to pad to 3 digits)
There ya go its all set!Cheers!

Comments: Post a Comment

<< Home

This page is powered by Blogger. Isn't yours?

© Copyright 2005, Rohan Thomas
This Blog contains findings that are self-explored and expressions that are self-evoked. They are by no meanss representions of my employer's view.