0
**MyTable**
*myColumn*
/A/B/C/XYZ
/A/B/C/abc
...

/A/B/C/whatever is not fixed and is dynamic and need to replaced with something like /1/2/3/whatever, and I like to changed it something like below, keeping the last part after last / intact.

/1/2/3/XYZ
/1/2/3/abc
...

I run the below query 

SELECT SUBSTRING(myColumn, 0,  len(myColumn) - CHARINDEX('/',REVERSE(myColumn)) +1 )
FROM MyTable

that correctly returns the part that need to be replaced

/A/B/C
/A/B/C
/A/B/C
...

The generic update is that I understood from UPDATE and REPLACE part of a string is 

Update MyTable SET myColumn = REPLACE(myColumn, '/A/B/C','/1/2/3')

So my final query to update and replace is 

Update MyTable
SET myColumn = REPLACE(myColumn,
                       (SELECT SUBSTRING(myColumn, 0,  len(myColumn) - CHARINDEX('/',REVERSE(myColumn)) +1 )
                        FROM MyTable),
                       '/1/2/3')

but it gives the below error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I suspect that the output of my subquery doesn't contain the quotes '...' , how to put that or what else is the issue?

Community
  • 1
  • 1
AKS
  • 1,279
  • 11
  • 27
  • You need a condition in the sub-select so it returns only one row! (Actually, no sub-select is needed, replace it with the column name instead.)' – jarlh Jul 01 '15 at 10:05
  • @jarlh , thanks for clue, I need to update all the rows, and every row may return respective values, so I expect there is only one value for each row for that column that need to be replaced. how can we select only one row, provided that the first part /X/Y/Z is dynamic and can differ for each row. – AKS Jul 01 '15 at 10:09

1 Answers1

1

Can you try this one:

UPDATE myTable
SET myColumn = '/1/2/3/' + REVERSE(SUBSTRING(REVERSE(myColumn),0,CHARINDEX('/',REVERSE(myColumn))))

It should insert /1/2/3/ in front and keep the part after last / from the original column value.

EDIT:

By the way, your query would also work if you removed SELECT FROM part.

UPDATE myTable
SET myColumn = REPLACE(myColumn, SUBSTRING(myColumn, 0, len(myColumn) - CHARINDEX('/',REVERSE(myColumn)) +1), '/1/2/3')

Problem is, your query with select returned all the rows from your table and tried to update your single row value with values from subquery. That's why you got an error.

msmolcic
  • 6,407
  • 8
  • 32
  • 56
  • :) thank you , your solution works like a charm. Accepting it as answer. It helped me and sure will help others. Regards, – AKS Jul 01 '15 at 11:59