Converting, Formatting, and Padding Values in T-SQL

This is another hat tip (HT) to my fellow coffee drinker co-worker Anne for some hard work in figuring out some tricky code.

Anne is working on a data conversion project between two systems. She needs to take some data and convert it to decimal format, round the value to remove the decimal places, and then pad it to the left to 9 places, to have leading 0s in front of the data.

While this task isn’t difficult, this is a little tricky, especially considering you have to convert the values to various types in order to to parse into other data types and do the padding. PL/SQL has a nice LPAD function for padding strings that T-SQL is currently lacking.

After a bit of testing, tweaking, and brain mungling (a technical term) the following solution was found:

SELECT CAST(REPLACE(STR(CAST(ROUND(TableName.ColumnToParse, 2, 0) AS DECIMAL(9,0)), 9), ' ' , '0') AS CHAR(9))

See, LOTS of casting and wrapping and converting, but it works, and I’m keeping dibs on this for the next time I have to deal with something similar.

About these ads

What are your 10 bits on the matter? I want to know!

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