How to pad zeroes to a substring in SQL?

Question

I have a varchar type column having text like 20171126-401-9-4496. The string has 4 sections with the fixed length for each section:

Section 1 - 8 digits

Section 2 - 4 digits

Section 3 - 2 digits

Section 4 - 4 digits

I would like to pad 2nd and 3rd section of the string with a '0'so that the text becomes 20171126-0401-09-4496 which satisfies the fixed length requirement of each section. I tried with SUBSTRING and CHARINDEX functions but was unable to get the desired result. How to solve this issue?

Post your attempt with substring and charindex, and the error you got, so we can debug it. Should a zero always be added, or does it depend on the data in the string? — Tab Alleman
– Tab Alleman, Commented Nov 29, 2017 at 19:03

Eric J. Price · Accepted Answer · 2017-11-29 19:20:12Z

6

What a perfect time for a PARSENAME hack!

SELECT PARSENAME(REPLACE(MyColumn,'-','.'),4)+'-'+ RIGHT('0000'+PARSENAME(REPLACE(MyColumn,'-','.'),3),4)+'-'+ RIGHT('00'+PARSENAME(REPLACE(MyColumn,'-','.'),2),2)+'-'+ PARSENAME(REPLACE(MyColumn,'-','.'),1)

edited Nov 29, 2017 at 19:20

Eric J. Price

2,7851 gold badge17 silver badges22 bronze badges

answered Nov 29, 2017 at 19:12

Tab Alleman

31.8k7 gold badges41 silver badges54 bronze badges

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

3 Comments

S3S Over a year ago

this is reversed when i run it oddly enough. but i like the use of parsename

S3S Over a year ago

added a deleted answer with changes. I'd just add the condition for the first and last columns too for fun

Tab Alleman Over a year ago

I don't use parsename often enough. I keep forgetting that the sections are numbered in descending order. Thanks for the edit.

S3S · Accepted Answer · 2017-11-29 19:19:41Z

Taking on what Tab Allemen did, you can apply it to the first and last columns as well...

declare @var varchar(18) = '201711-1-9-4496' SELECT right('00000000' + parsename(replace(@var,'-','.'),4),8) + '-' + right('0000' + parsename(replace(@var,'-','.'),3),4) + '-' + right('00' + parsename(replace(@var,'-','.'),2),2) + '-' + right('0000' + parsename(replace(@var,'-','.'),1),4)

Radu · Accepted Answer · 2017-11-29 20:23:44Z

If you always want to insert a '0' on those positions then it will work like that:

select stuff (stuff('20171126-401-9-4496',14,0,0),10,0,'0')

If we have a variable length of fields then it will work like this (example for the first field):

select @str = '20175555-0001-9-46' select @len = (select charindex('-',substring(@str,10,100))) select case when @len = 2 then stuff(@str,10,0,'000') when @len = 3 then stuff(@str,10,0,'00') when @len = 4 then stuff(@str,10,0,'0') else @str end as string

this is close but doesn't work for, say, ''20175555-1-9-46''
@scsimon I've updated my answer for variable length, example is for the first. field

Zorkolot · Accepted Answer · 2017-11-29 21:40:51Z

If you really love derived tables...

DECLARE @mytable TABLE (mynewstring varchar(100)) --replace @mytable with your table name INSERT INTO @mytable VALUES ('20171126-401-9-4496') ,('1-401-9-4496') ,('1-1-1-1') ,('---') SELECT dT4.FirstVal + SecondVal + ThirdVal + FourthVal [newval] FROM ( SELECT LEFT('00000000', len('00000000') - (dT3.[firstD] - 1)) + LEFT(mynewstring, [firstD]) [FirstVal] ,LEFT('0000', len('0000') - ([secondD] - [firstD] - 1)) + SUBSTRING(mynewstring, [firstD] + 1, [secondD] - [firstD]) [SecondVal] ,LEFT('00', len('00') - ([thirdD] - [secondD] - 1)) + SUBSTRING(mynewstring, [secondD] + 1, [thirdD] - [secondD]) [ThirdVal] ,LEFT('0000', len('0000') - (len(mynewstring) - [thirdD])) + SUBSTRING(mynewstring, [thirdD] + 1, len(mynewstring) - [thirdD]) [FourthVal] FROM ( SELECT dT2.* ,CHARINDEX('-', dT2.mynewstring, dT2.[secondD] + 1) [thirdD] FROM ( SELECT dT.* ,CHARINDEX('-', dT.mynewstring, dT.[firstD]+1) [secondD] FROM ( SELECT mynewstring ,CHARINDEX('-', mynewstring, 0) [firstD] FROM @mytable ) AS dT ) AS dT2 ) AS dT3 ) AS dT4

Would produce output:

newval 20171126-0401-09-4496 00000001-0401-09-4496 00000001-0001-01-0001 00000000-0000-00-0000

The algorithm grabs the position of the dashes, sequentially. It then does string concatenation with 0's for that position. Finally, the values are concatenated.

Matt · Accepted Answer · 2017-11-29 22:19:51Z

To round out and show a split string method in case someone looks at this question and has 5 or more parts when parsename hack wont work.

DECLARE @Table AS TABLE (col1 VARCHAR(50)) INSERT INTO @Table VALUES ('20171127-4301-9-4496'),('20171125-301-9-4496'),('20171124-21-9-4496'),('20171123-1-9-4496'),('20171122-1-09-4496') SELECT * ,NewFormat = p1 + '-' + RIGHT('0000' + P2, 4) + '-' + RIGHT('00' + P3,2) + '-' + P4 FROM ( SELECT col1, xmlcol1 = CAST(('<row><col>' + REPLACE(col1,'-','</col><col>') + '</col></row>') AS XML) FROM @Table ) t CROSS APPLY (SELECT t.n.value('col[1]','VARCHAR(50)') as P1 ,t.n.value('col[2]','VARCHAR(50)') as P2 ,t.n.value('col[3]','VARCHAR(50)') as P3 ,t.n.value('col[4]','VARCHAR(50)') as P4 FROM t.xmlcol1.nodes ('/row') AS t(n)) c

You can also extend so that you can test if the part of the string has more than 4 characters etc and not pad it or what ever.

Collectives™ on Stack Overflow

How to pad zeroes to a substring in SQL?

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

3 Comments

Comments

2 Comments

Comments

Comments

Related