Need to write a SQL query to fetch data from a Oracle database

Question

I need to write a SQL query which can fetch a data from one table A.

Senario - Lets take table A has only two column C1 and C2. C1 has row_id's and C2 has vaues like "Site=google;site=gmail,site=yahoo"

Requirment - Need to write a query which can fetch all the row_id from column C1 of table A but the value should come for column C2 as "google;gmail;yahoo". Means it should not show "Site=" for all the values of C2 column in the data fetch. And one more condition that if there is , in place of ; in the value then query should convert it into ; and show the data.

Bob Jarvis - Слава Україні · Accepted Answer · 2011-02-23 13:47:50Z

4

How about this:

SELECT C1, REPLACE(REPLACE(C2, 'Site=', ''), ',', ';') AS C2
  FROM TABLE

Share and enjoy.

answered Feb 23, 2011 at 13:47

Bob Jarvis - Слава Україні

50.2k10 gold badges81 silver badges119 bronze badges

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

3 Comments

Rene Over a year ago

You need another replace to take Site= and site= into account: SELECT REPLACE(replace(replace('Site=google;site=gmail,site=yahoo', 'Site=', ''), 'site=', ''), ',', ';') AS C2 FROM dual

Vicky Over a year ago

Thanks for your help..I got resuld :)

Bob Jarvis - Слава Україні Over a year ago

@Rene - I thought about that, but decided it was better to leave it as posted to keep it clearer. The OP can add more REPLACE's (or change it to REPLACE(LOWER(C2), 'site=', '') if wanted.

Collectives™ on Stack Overflow

Need to write a SQL query to fetch data from a Oracle database

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related