Click here to Skip to main content
15,849,180 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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

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.
 
Share this answer
 
v2
Comments
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 :-)
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
 
Share this answer
 
Comments
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
Use simple UPDATE[^] statement, like:
SQL
UPDATE TableName SET ID = LEFT(ID, 8) + '-04-' + RIGHT(ID, LEN(ID)-8)


For further information, please see:
LEFT[^]
RIGHT[^]
 
Share this answer
 
Comments
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
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
 
Share this answer
 
v4
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.
 
Share this answer
 
v2
Comments
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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900