如何在Table SQL Server中的每一行上添加字符串 [英] How to add string on each row in Table SQL server
问题描述
我有一张桌子
ID LDID CusDI
20140411002 LD0000240301 003045
20140411005 LD0000350301 003074
20140411007 LD0000250301 003049
20140411008 LD0000390301 003073
20140411009 LD0000300301 003058
我必须将其更改为
ID LDID CusDI
20140411-04-002 LD0000240301 003045
20140411-04-005 LD0000350301 003074
20140411-04-007 LD0000250301 003049
20140411-04-008 LD0000390301 003073
20140411-04-009 LD0000300301 003058
I Have a table
ID LDID CusDI
20140411002 LD0000240301 003045
20140411005 LD0000350301 003074
20140411007 LD0000250301 003049
20140411008 LD0000390301 003073
20140411009 LD0000300301 003058
And i have to change it to
ID LDID CusDI
20140411-04-002 LD0000240301 003045
20140411-04-005 LD0000350301 003074
20140411-04-007 LD0000250301 003049
20140411-04-008 LD0000390301 003073
20140411-04-009 LD0000300301 003058
推荐答案
遍历您的表列ID,并使用 STUFF添加'-04-',如下所示
loop through your table column ID and add '-04-' like below using STUFF
STUFF ( ID, 9, 0, '-04-' )
STUFF(character_expression,start,length,replaceWith_expression)
STUFF ( character_expression , start , length , replaceWith_expression )
STUFF函数将字符串插入另一个字符串。它删除起始位置的第一个字符串中指定长度的字符,然后将第二个字符串插入起始位置的第一个字符串。
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
然后使用更新表格帮助子串
试试吧,
Then Update your table with help ofSubstring
Try it ,
create table #dummy(ID nvarchar(max), LDID nvarchar(max), CusDI nvarchar(max))
insert into #dummy values('20140411002', 'LD0000240301' ,'003045')
insert into #dummy values('20140411005 ', 'LD0000240301' ,'003074')
insert into #dummy values('20140411007 ', 'LD0000240301' ,'003049')
insert into #dummy values('20140411008 ', 'LD0000240301' ,'003073')
insert into #dummy values('20140411009', 'LD0000240301' ,'003058')
select *From #dummy
update B set b.id=SUBSTRING(a.id,0,9)+'-04-'+SUBSTRING(a.ID,9,3) from #dummy as a inner join #dummy as b on a.CusDI=b.CusDI
select *From #dummy
使用简单更新 [ ^ ]语句,如:
Use simple UPDATE[^] statement, like:
UPDATE TableName SET ID = LEFT(ID, 8) + '-04-' + RIGHT(ID, LEN(ID)-8)
这篇关于如何在Table SQL Server中的每一行上添加字符串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!