0

I have a table like

id  request    response 
1   question1  option 1, option 2 , option 3
2   question2   option 1, option 2

I WANT to display if question1 select

option 1
option 2 
option 3
Layra
  • 3
  • 2
  • 3
    Hi Layra, at the very least include a tag for the specific database product you are using. Each database product implements a dialect of SQL and solutions for each database product may vary wildly. – TT. May 31 '21 at 05:48
  • 1
    Never, ever store data as comma separated items! It will only cause you lots of trouble. – jarlh May 31 '21 at 07:14

2 Answers2

0

If you're using Oracle, I believe you're looking for something like this -

select regexp_substr(response,'[^,]+', 1, level) from ReqResponseTable
  where id = 1
  connect by regexp_substr(response, '[^,]+', 1, level) is not null;

But I do agree with jarlh's suggestion that storing comma-separated values is not the recommended way to go. If you've just started with this approach, better change this and store multiple records instead. However, be mindful of the fact that 'id' column alone won't act as your Primary Key then.

id request response
1 question1 option1
1 question1 option2
1 question1 option3
2 question2 option1
2 question2 option2
Rajeev Pande
  • 456
  • 3
  • 8
0

Basically same question as that of SQL split values to multiple rows

So in your case query will be like this: (Note: I have assumed your table name as requestresponse and MySQLDialect )

  SELECT
  
  SUBSTRING_INDEX(SUBSTRING_INDEX(requestresponse.response, ',', numbers.n), ',', -1) response
from
 ( select 1 n union all
   select 2 union all select 3 union all
   select 4 union all select 5)  numbers INNER JOIN requestresponse
  on CHAR_LENGTH(requestresponse.response)
     -CHAR_LENGTH(REPLACE(requestresponse.response, ',', ''))>=numbers.n-1
     where request = 'question1'
order by
  id, n;

But also as everyone here have suggested, try to avoid storing data in comma separated values format.

Vishal Vyavahare
  • 21
  • 2