如何在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 )

Quote：

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 of Substring

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)

详情请见：

LEFT [ ^ ]

权利 [ ^ ]

这篇关于如何在Table SQL Server中的每一行上添加字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！