Clever Geek Handbook

How to replace specific text with query result

My request:

select SeqNo, Name, Qty, Price 
from vendor 
where seqNo = 1;

as shown below:

SeqNo   Name    Qty  Price
1       ABC     10   11
1       -do-    11   12
1       ditto   13   14

The above result shows the vendor name as ABC on the first line, which is correct. Later, when users entered the same manufacturer name "ABC"

as either '-do-'

/ 'ditto'

. Now, in my final query output, I want to replace -do-

and ditto

with ABC

(as in the above example), so my final output should look like this:

SeqNo   Name    Qty  Price
1       ABC     10   11
1       ABC     11   12
1       ABC     13   14
+3


source to share


4 answers


this works in sql server for sample data. Don't know what your other lines look like.



select SeqNo,
       case when Name in ('-do-','ditto') then 
        (select Name from test where Name not in('-do-','ditto')
        and SeqNo = 1)
        else Name
        end as Name
from table
where SeqNo = 1
+2


source


Use the function REPLACE 



SELECT SeqNo, REPLACE(REPLACE(Name,'ditto','ABC'),'-do-','ABC'), Qty, Price
FROM vendor
WHERE seqNo = 1;
0


source


Use CASE

:

select SeqNo,
       case when Name in ('-do-','ditto') then 'ABC' else Name end as name,
       Qty,
       Price 
from vendor 
where seqNo = 1;
0


source


Apart from other answers, if your requirement only includes the sample data you submitted =), this will do:

 SELECT SeqNo, 'ABC' Name, Qty, Price
   FROM vendor
  WHERE seqNo = 1;
0


source






More articles:


All Articles