CODEWITHSUNDEEP
sqlsql-server
PigsIncorporated
PigsIncorporated

Reputation: 153

SQL Command to zero pad a column value

I have a database column that is a text string. Some of the values are like

"12345"

and some are as year + sequential number like:

 "2016-1, 2016-2, 2016-3, 2017-1, 2017-2, 2017-3" etc.

I want to update the column values to 

"2016-001, 2016-002, 2016-003, 2017-001, 2017-002, 2017-003"

for the entire table. I'm not sure how to do this. Any help would be appreciated. I already updated my stored procedure as such to generate new numbers with zero padding like:

rptnum = cast(year(getdate()) as varchar)
 + '-' + RIGHT('000'+ISNULL(Cast((select count(*) 
from dbo.tablename where rptyr = (year(getdate()))) + 1 as varchar),''),3),

Upvotes: 0

Views: 156

Answers (1)

dean
dean

Reputation: 10098

Something like this should do:

select left(rptnum, charindex('-', rptnum))+right('000'+substring(rptnum, charindex('-', rptnum)+1, 10), 3)

Upvotes: 1

Related Questions