15,849,180 members
See more: , +
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
Posted

## Solution 1

loop through your table column ID and add '-04-' like below using STUFF
SQL
`STUFF ( ID, 9, 0, '-04-' )`

STUFF ( character_expression , start , length , replaceWith_expression )

Quote:
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.

v2
Maciej Los 29-Oct-14 6:28am
+5
Dilan Shaminda 29-Oct-14 6:36am
Thank you :-)
phanithly 9-Nov-14 20:29pm
Thank you so much It work 100%
Dilan Shaminda 9-Nov-14 21:19pm
you are welcome :-)

## Solution 2

Then Update your table with help of `Substring`

Try it ,
SQL
```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```

Maciej Los 29-Oct-14 6:28am
+5
King Fisher 29-Oct-14 6:58am
Thank you Boss :)
phanithly 9-Nov-14 20:31pm
Thank you ! finally it work

## Solution 5

Use simple UPDATE[^] statement, like:
SQL
`UPDATE TableName SET ID = LEFT(ID, 8) + '-04-' + RIGHT(ID, LEN(ID)-8)`

LEFT[^]
RIGHT[^]

King Fisher 29-Oct-14 7:03am
wow. you did Simply :)
Maciej Los 29-Oct-14 10:00am
Thank you, King_Fisher ;)
King Fisher 30-Oct-14 4:08am
Assist me :)
http://www.codeproject.com/Questions/834806/How-do-I-Select-This-Result-Set

## Solution 3

SQL
```create table table_name(ID nvarchar(max),LDID nvarchar(max),CusDI nvarchar(max));

insert into table_name(ID,LDID,CusDI)values('20140411002','LD0000240301','003045');
insert into table_name(ID,LDID,CusDI)values('20140411005','LD0000350301','003074');
insert into table_name(ID,LDID,CusDI)values('20140411007','LD0000250301','003049');
insert into table_name(ID,LDID,CusDI)values('20140411008','LD0000390301','003073');
insert into table_name(ID,LDID,CusDI)values('20140411009','LD0000300301','003058');

SELECT cast(substring(replace(ID,',',''),0,len(replace(ID,',',''))-2) as nvarchar)+'-04-'+cast(substring(replace(ID,',',''),9,len(replace(ID,',',''))-2) as nvarchar) as ID,LDID,CusDI from table_name```

v4

## Solution 4

You can directly update your table for column ID as below

SQL
`Update table set ID=Replace(ID,'2014041100','20140411-04-00')`

if the preceding character are same for given column.

v2
King Fisher 29-Oct-14 8:07am
hard-coding ,Op have Sample records ,ID may be '20140411002'..
20140411010-20140411099 ...20140411100-20140411999.in this case you can't use this as one.you need to change the Query for Every Conditions.
Shweta N Mishra 29-Oct-14 8:17am
correct this can b applied only if preceding characters are same. Otherwise
using Stuff,substring and Left , right function are the better options.
King Fisher 29-Oct-14 8:20am
:)
phanithly 9-Nov-14 20:34pm
:) it work but if i have more records. so i will hard-coding right ?
Shweta N Mishra 10-Nov-14 3:34am
Try the 1st solution, select STUFF ( ID, 9, 0, '-04-' ) by Dilan. That would be optimal zoluion in your case.