How to insert character in between a string in SQL

Question

I have a col1 in tbl1 which has data as under:

C:\ABC\1245_PQR\125\xyz\ROW20MAOSAD12\

now I want to insert a '\' after C:\ABC\1245_PQR\125\xyz\ROW and before '20MAOSAD12\'

and the data format is same but it changes in all row, for example -

C:\ABC\1245_PQR\125\xyz\ROW20MAOSAD12\
C:\ABC\3456_ADR\515\xpo\ROWadMAOSAD23\
C:\ABC\1547_DFR\255\RDS\ROW14SDFS15\

Can someone please help. Thanks

Donald · Accepted Answer · 2018-02-02 03:30:30Z

2

You can use replace function

SELECT REPLACE('C:\ABC\1245_PQR\125\xyz\ROW20MAOSAD12\', '\ROW', '\ROW\')

To be more precise

SELECT REPLACE(col1, '\ROW', '\ROW\') from tbl1

edited Feb 2, 2018 at 3:30

answered Feb 2, 2018 at 1:53

Donald

715 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Tim Biegeleisen Over a year ago

You might want to match the leading forward slash as well, i.e. find on \ROW and replace with \ROW\ .

Duffer Over a year ago

Hi Donald,Thanks. But it didnt work. It just updated one row not all the rows in the column. I have another col2 which is comman for all the rows I want to update, Can I use it somehow. Thanks

Donald Over a year ago

I added a SQL for you scenario and the one I provided is just a sample.

russ · Accepted Answer · 2018-02-02 03:37:05Z

0

Alternatively you could consider the STUFF operator:

STUFF ( character_expression , start , length , replaceWith_expression )  

SELECT STUFF('C:\ABC\1245_PQR\125\xyz\ROW20MAOSAD12\',28,0, '\')

SELECT STUFF('SomeColumn',28,0, '\') FROM SomeTable WHERE SomeColumn=SomeValue

answered Feb 2, 2018 at 3:37

russ

6244 silver badges7 bronze badges

Comments

Anagha · Accepted Answer · 2018-02-02 11:08:24Z

Also, I made this one - a bit lengthy

-- Step 1 Select all rows that have ...xyz.... in col1 and col2 is xyz

select * from tbl where col2 = 'row' and col1 like '%xyz%'

-- Step 2 -- From the rows above, calculate the start index of ...xyz....... in col1

select charindex('xyz', col1) from (select col1  from tbl where col2 = 'xyz' and col1 like '%xyz%') tmp

-- Step 3 -- Split the col1 from CharIndex + 3 (size of xyz)

select left(col1, charindex('xyz', col1) + 2 ), substring(col1, charindex('xyz', col1) + 3, LEN(col1) ), ( left(col1, charindex('xyz', col1) + 2 ) + '\' + substring(col1, charindex('xyz', col1) + 3, LEN(col1) ))
from (select col1  from tbl where name = 'xyz' and col1 like '%xyz%') tmp

-- Step 4 -- Update !!

update tbl
SET col1 = ( left(col1, charindex('xyz', col1) + 2 ) + '\' + substring(col1, charindex('xyz', col1) + 3, LEN(col1) ))
where col2 = 'xyz' and col1 like '%xyz%' and col1 not like '%xyz\%'

Collectives™ on Stack Overflow

How to insert character in between a string in SQL

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related