entityMap|blocks|key|9oaro|text|对于PARSENAME黑客来说，这是一个多么完美的时刻！|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|9jv3v|SELECT+PARSENAME(REPLACE(MyColumn,'-','.'),4)%2B'-'%2B
+++++++RIGHT('0000'%2BPARSENAME(REPLACE(MyColumn,'-','.'),3),4)%2B'-'%2B
+++++++RIGHT('00'%2BPARSENAME(REPLACE(MyColumn,'-','.'),2),2)%2B'-'%2B
+++++++PARSENAME(REPLACE(MyColumn,'-','.'),1)|code-block|syntax|javascript|e4s29^0|2|9|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|M|9|@$A|N|B|O|C|D]]|E|@]|F|$]]|$2|G|4|H|6|I|8|P|9|@]|E|@]|F|$J|K]]|$2|L|4|-4|6|7|8|Q|9|@]|E|@]|F|$]]]]

What a perfect time for a <code>PARSENAME</code> hack!

<pre><code>SELECT PARSENAME(REPLACE(MyColumn,'-','.'),4)+'-'+
 RIGHT('0000'+PARSENAME(REPLACE(MyColumn,'-','.'),3),4)+'-'+
 RIGHT('00'+PARSENAME(REPLACE(MyColumn,'-','.'),2),2)+'-'+
 PARSENAME(REPLACE(MyColumn,'-','.'),1)
</code></pre>

entityMap|0|type|IMAGE|mutability|IMMUTABLE|data|imageUrl|https://i.stack.imgur.com/bYEtr.jpg|imageAlt|blocks|key|8s5ut|text|如果你总是想在这些位置上插入一个'0‘，那么它的工作原理是这样的：|unstyled|depth|inlineStyleRanges|entityRanges|6290o|select+stuff+(stuff('20171126-401-9-4496',14,0,0),10,0,'0')|code-block|syntax|javascript|9dpjc|​|c69rd|?|atomic|offset|length|63kti|4vnj1|如果我们有可变长度的字段，那么它将像这样工作(第一个字段的例子)：|5lfge|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|ng6d^0|0|0|0|0|1|0|0|0|0|0^^$0|$1|$2|3|4|5|6|$7|8|9|-4]]]|A|@$B|C|D|E|2|F|G|11|H|@]|I|@]|6|$]]|$B|J|D|K|2|L|G|12|H|@]|I|@]|6|$M|N]]|$B|O|D|P|2|F|G|13|H|@]|I|@]|6|$]]|$B|Q|D|R|2|S|G|14|H|@]|I|@$T|15|U|16|B|17]]|6|$]]|$B|V|D|P|2|F|G|18|H|@]|I|@]|6|$]]|$B|W|D|X|2|F|G|19|H|@]|I|@]|6|$]]|$B|Y|D|Z|2|L|G|1A|H|@]|I|@]|6|$M|N]]|$B|10|D|-4|2|F|G|1B|H|@]|I|@]|6|$]]]]

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

<pre><code>select stuff (stuff('20171126-401-9-4496',14,0,0),10,0,'0')
</code></pre>

<a href="https://i.stack.imgur.com/bYEtr.jpg" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/bYEtr.jpg" alt="enter image description here"></a>

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

<pre><code>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
</code></pre>

entityMap|blocks|key|4ojor|text|采用Tab+Allemen所做的，您也可以将其应用于第一列和最后一列……|type|unstyled|depth|inlineStyleRanges|entityRanges|data|157ka|declare+@var+varchar(18)+=+'201711-1-9-4496'


SELECT+right('00000000'+%2B+parsename(replace(@var,'-','.'),4),8)+%2B+'-'
++++++++%2B+right('0000'+%2B+parsename(replace(@var,'-','.'),3),4)+%2B+'-'
++++++++%2B+right('00'+%2B+parsename(replace(@var,'-','.'),2),2)+%2B+'-'
++++++++%2B+right('0000'+%2B+parsename(replace(@var,'-','.'),1),4)+|code-block|syntax|javascript|986s8^0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|I|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|J|9|@]|A|@]|B|$F|G]]|$2|H|4|-4|6|7|8|K|9|@]|A|@]|B|$]]]]

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

<pre><code>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) 
</code></pre>

entityMap|blocks|key|8t2mp|text|如果你真的喜欢派生表...|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2gcbg|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+%2B+SecondVal+%2B+ThirdVal+%2B+FourthVal+[newval]
++FROM+(
SELECT+LEFT('00000000',+len('00000000')+-+(dT3.[firstD]+-+1))+%2B+LEFT(mynewstring,+[firstD])+[FirstVal]
++++++,LEFT('0000',+len('0000')+-+([secondD]+-+[firstD]+-+1))+%2B+SUBSTRING(mynewstring,+[firstD]+%2B+1,+[secondD]+-+[firstD])+[SecondVal]
++++++,LEFT('00',+len('00')+-+([thirdD]+-+[secondD]+-+1))+%2B+SUBSTRING(mynewstring,+[secondD]+%2B+1,+[thirdD]+-+[secondD])+[ThirdVal]
++++++,LEFT('0000',+len('0000')+-+(len(mynewstring)+-+[thirdD]))+%2B+SUBSTRING(mynewstring,+[thirdD]+%2B+1,+len(mynewstring)+-+[thirdD])+[FourthVal]
++FROM+(
SELECT+dT2.*
++++++,CHARINDEX('-',+dT2.mynewstring,+dT2.[secondD]+%2B+1)+[thirdD]
++FROM+(
SELECT+dT.*
++++++,CHARINDEX('-',+dT.mynewstring,+dT.[firstD]%2B1)+[secondD]
++FROM+(
SELECT+mynewstring++
++++++,CHARINDEX('-',+mynewstring,+0)+[firstD]++++
++FROM+@mytable
+++++++)+AS+dT++++++
+++++++)+AS+dT2
+++++++)+AS+dT3
+++++++)+AS+dT4|code-block|syntax|javascript|ci97f|将产生输出：|fu4jc|newval
20171126-0401-09-4496
00000001-0401-09-4496
00000001-0001-01-0001
00000000-0000-00-0000|fhrgl|该算法按顺序获取虚线的位置。然后，它对该位置使用0进行字符串连接。最后，将这些值连接起来。|frl86^0|0|0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|O|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|P|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|Q|9|@]|A|@]|B|$]]|$2|J|4|K|6|E|8|R|9|@]|A|@]|B|$F|G]]|$2|L|4|M|6|7|8|S|9|@]|A|@]|B|$]]|$2|N|4|-4|6|7|8|T|9|@]|A|@]|B|$]]]]

If you really love derived tables...

<pre><code>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
</code></pre>

Would produce output:

<pre><code>newval
20171126-0401-09-4496
00000001-0401-09-4496
00000001-0001-01-0001
00000000-0000-00-0000
</code></pre>

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.

entityMap|blocks|key|488ag|text|当parsename+hack不起作用时，如果有人看到这个问题，并且有5个或更多的部分，为了四舍五入并显示拆分字符串方法。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|echo0|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+%2B+'-'+%2B+RIGHT('0000'+%2B+P2,+4)+%2B+'-'+%2B+RIGHT('00'+%2B+P3,2)+%2B+'-'+%2B+P4
FROM
++++(
+++++++SELECT+col1,+xmlcol1+=+CAST(('<row><col>'+%2B+REPLACE(col1,'-','</col><col>')+%2B+'</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|code-block|syntax|javascript|bua0k|您还可以扩展，以便可以测试字符串的一部分是否超过4个字符，等等，而不填充它或其他任何东西。|64cbi^0|0|0|0^^$0|$]|1|@$2|3|4|5|6|7|8|K|9|@]|A|@]|B|$]]|$2|C|4|D|6|E|8|L|9|@]|A|@]|B|$F|G]]|$2|H|4|I|6|7|8|M|9|@]|A|@]|B|$]]|$2|J|4|-4|6|7|8|N|9|@]|A|@]|B|$]]]]

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.

<pre><code>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(('&lt;row&gt;&lt;col&gt;' + REPLACE(col1,'-','&lt;/col&gt;&lt;col&gt;') + '&lt;/col&gt;&lt;/row&gt;') 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
</code></pre>

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.

I have a varchar type column having text like <code>20171126-401-9-4496</code>. 
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
<code>20171126-0401-09-4496</code> which satisfies the fixed length requirement of each section. I tried with <code>SUBSTRING</code> and <code>CHARINDEX</code> functions but was unable to get the desired result. How to solve this issue?

How to pad zeroes to a substring in SQL?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

 我有一个varchar类型的列，其文本类似于20171126-401-9-4496。字符串有4个部分，每个部分的长度是固定的： 第1-8位 第2-4位数字 第3-2位数字 第4-4位 我想在字符串的第二部分和第三部分填充一个‘0’，这样文本就变成了20171126-0401-09-4496，满足了每个部分的固定长度要求。我尝试了SUBSTRING和CHARINDEX函数，但没有得到想要的结果。这

问如何在SQL中将零填充到子字符串？
EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中将零填充到子字符串？EN

回答 5

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何在SQL中将零填充到子字符串？
EN