CODEWITHSUNDEEP
sqlsql-server
Programmer
Programmer

Reputation: 8717

How to replace a specific text from the query result

My query:

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

outputs like below:

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

The output above shows the vendor name as ABC in first row which is correct. Later on as users entered for the same vendor name "ABC" as either '-do-' / 'ditto'. Now in my final query output I want to replace -do- and ditto with ABC (as in above example) so my final output should look like:

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

Upvotes: 3

Views: 1845

Answers (4)

Sachu
Sachu

Reputation: 7766

this is working in sql server for you sample data..not sure how your other rows are 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

Upvotes: 2

tjeloep
tjeloep

Reputation: 318

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

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

Upvotes: 0

jarlh
jarlh

Reputation: 44766

Use CASE:

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

Upvotes: 0

Matt
Matt

Reputation: 15071

Use the REPLACE function

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

Upvotes: 0

Related Questions