formatting a vachar column

[runnerlk]

I have a SQL server 2005 table with a varvhar(50) column that is populated with values ranging from 01 to 9999. I would like to pad each row to 5 characters with leading 0's and then prepend the column with CP so that so that all data in that column is 7 characters in the following format CPxxxxx: where xxxxx is a value between 01 and 9999.

If someone has done something like this and help would be greatly appreciated.

Thanks,

Lou

 

[gmmastros]

There are a combination of built in functions that you can use to do this.

Select 'CP' + Right('00000' + YourColumnName, 5) As NewColumnvalue
From   YourTableName

Basically.... you start by adding five 0's to the left of your data.

[tt]
01 -> 0000001
9999 -> 000009999[/tt]

Then, take the right 5 characters.

[tt]
01 -> 0000001 -> 00001
9999 -> 000009999 -> 09999[/tt]

Then, add CP to it. Make sense?


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[runnerlk]

George,

Yes the logic works as intended. How do I then update the column with the formatted values?

Thanks,

Lou

 

[gmmastros]

I suggest that you first make a good backup of your database, just in case....

Update YourTableName
Set    YourColumnName = 'CP' + Right('00000' + YourColumnName, 5) 
[!]Where  YourColumnName Not Like 'CP%'[/!]

I put the where clause in there so that (accidentally) running this code twice will not update the column twice.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[runnerlk]

George,

worked perfectly thanks.