SQL Command to zero pad a column value

Question

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),

Is it always yyyy-n, with only 1 digit after the dash? Or can it be several digits after the dash? — jarlh
– jarlh, Commented Feb 22, 2017 at 14:02
Well it can be several after it gets to higher numbers. 2016-10, 2016-100. It never exceeds 3 for each year. — PigsIncorporated
– PigsIncorporated, Commented Feb 22, 2017 at 14:03
getdate() takes current year, so you would have 2017 everywhere — Whencesoever
– Whencesoever, Commented Feb 22, 2017 at 14:07
That function is on an approval. So it basically assigns the next number in sequence with other numbers assigned for the year. — PigsIncorporated
– PigsIncorporated, Commented Feb 22, 2017 at 14:11

dean · Accepted Answer · 2017-02-22 14:18:04Z

1

Something like this should do:

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

answered Feb 22, 2017 at 14:18

dean

10.1k2 gold badges27 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Command to zero pad a column value

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related