How to add string on each row in Table SQL server

Question

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 28-Oct-14 17:14pm

phanithly

Add a Solution

5 solutions

Solution 5

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[^]

Posted 29-Oct-14 0:32am

Maciej Los

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dilan Shaminda · Accepted Answer · 2014-10-28T18:18:00

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.

Posted 28-Oct-14 18:18pm

Dilan Shaminda

Updated 28-Oct-14 18:19pm

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 :-)

King Fisher · Accepted Answer · 2014-10-28T18:34:00

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

kskumaran · Accepted Answer · 2014-10-28T18:55:00

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

Shweta N Mishra · Accepted Answer · 2014-10-28T23:13:00

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.

Posted 28-Oct-14 23:13pm

Shweta N Mishra

Updated 29-Oct-14 2:00am

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.

How to add string on each row in Table SQL server

5 solutions

Solution 1

Solution 2

Solution 5

Solution 3

Solution 4

Add your solution here

Preview 0