Developing Matt

My Technical Journal

Padding your numbers…just a little

leave a comment »

I know padding your numbers in this economic crisis is a touchy issue.  But let’s put our morals to the side for a moment and discuss how to do it.  OK.  Actually by ‘padding’ I’m really just meaning adding something extra flair  to your number.  Example

       boring example: changing your number from 36 to 00036 
       exciting example: changing your number from 36 to WOW_LOOK_I_AM_36

Formatting in t-sql is limited.  It doesn’t really care about how you want your data to look.  What you wear to work isn’t the datacenter’s job.  That’s your mother’s job. 

The new function I discovered is STR.  Not sure why I never used it before.  The second argument for STR is how many characters you want your number to be…and it fills it with spaces.  So STR(3,2) gives us the number 3 represented as a 2 character string like so: ‘ 3’.  So throw in a replace (change spaces to 0s) and you have your pad.  I placed mine in a computed column using an id column:

replace(str(id,3),’ ‘,0)

if you want to copy and paste the code below you can test it out

declare @myage int
set @myage = 36 –enter your age
select replace(str(@myage,3),’ ‘,’WOW_LOOK_I_AM_’)

Advertisements

Written by matt

June 3, 2009 at 6:25 am

Posted in T-Sql

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: