Find number of count of a word in column in each row

Question

I have a table name 'answer' and where answer saved given by a user for a question. User give multiple answer of a question.So answer save as string in 'answer' column. My table look like this

id  userid   questionid  answer
 
1   2        5           red,blue,white,green

2   3        5           red,blue

3   5        5           red,white

4   6        5           blue,white,green

5   7        5           red,blue,white,green

I want to select those userid whose answer 3 value(blue,white,green) or more than 3(red,blue,white,green). I have tried with length function but it not work.

this question has been answers multiple times http://stackoverflow.com/q/12344795/4323504 — Luthando Ntsekwa, Aug 04 '16 at 13:45
yes, you would put that in your where clause, okay i will add an answer for you — Luthando Ntsekwa, Aug 04 '16 at 13:49

Luthando Ntsekwa · Accepted Answer · 2016-08-04T13:57:06.967

0

Based on the link i provided on my comment ealier, you can accomplish what you want by doing this:

SELECT userid    
FROM AnswersTable 
WHERE ROUND (   
         (LENGTH(answer) - LENGTH( REPLACE ( answer, ",", "") ) ) / LENGTH(",")        
       ) > 2

edited Aug 04 '16 at 13:57

answered Aug 04 '16 at 13:51

Luthando Ntsekwa

4,192
6
23
52

one more question if in place of (,) if we use some ascii value so how can i do this? – ankit Aug 05 '16 at 06:53
what ASCII value you want to use? – Luthando Ntsekwa Aug 05 '16 at 06:58
'Unit Separator' and hax value for this '1F' – ankit Aug 05 '16 at 07:00
I think you should open another question, or try something like `'\x1F'` instead of `","` , remember 'Unit Separator' is unprintable, check here http://www.ascii-code.com/ – Luthando Ntsekwa Aug 05 '16 at 07:08
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/120205/discussion-between-ankit-and-the-flash). – ankit Aug 05 '16 at 07:09

Find number of count of a word in column in each row

1 Answers1