mysql select count of records from a string which have exact substring

Question

I have the five records which is like below. I need to fetch the data which have 'rock'. So I need the output like this, the output should display the count of 'rock', so according to the above database the output count should be "3" i.e., rec 1, rec 3 & rec 5". So the count should not take "rec 2 & rec 4".

rec1: [u'armenian rock', u' armenian', u' singer-songwriter', u' rock'] 
rec2: [u'punk', u' armenian rock', u' new wave', u' italian'] 
rec3: [u'rock']
rec4: [u'japanese', u' okuda tamio related', u" rock'n'roll", u' roots rock'] 
rec5: [u'test rock', u' armenian', u' singer-songwriter', u" rock"]

Better update the question that you are searching by `u rock` and not `rock` — G one, Mar 14 '14 at 07:45

score 0 · Answer 1 · answered Mar 14 '14 at 07:24

0

Try the string function

INSTR(str,substr)

In your query.

Something like this:

SELECT COUNT(*) FROM yourTable WHERE INSTR(yourTable.tags, 'rock') > 0;

Another option could be the use of

LOCATE(substr,str), LOCATE(substr,str,pos)

Please have a look into the mysql documentation:

http://dev.mysql.com/doc/refman/5.1/en/string-functions.html

answered Mar 14 '14 at 07:24

solick

2,325
3
17
29

It will count all instances of rock. – Mihai Mar 14 '14 at 07:25

score 0 · Answer 2 · answered Mar 14 '14 at 07:29

0

SELECT (LENGTH(tags) - LENGTH(REPLACE(tags, 'rock', ''))) / LENGTH('rock') AS count FROM your_table

Try this. Yes, looks bloated but as far as i know there is no any other possible solution.

answered Mar 14 '14 at 07:29

Arsen Ibragimov

425
4
18

score 0 · Answer 3 · answered Mar 14 '14 at 07:35

0

SELECT SUM((LENGTH(tags) - LENGTH(REPLACE(tags, 'u\' rock', ''))) / LENGTH('u\' rock'))as counting 
FROM tableName

FIDDLE

answered Mar 14 '14 at 07:35

Mihai

26,325
7
66
81

G one · Answer 4 · 2014-03-14T07:56:52.790

0

You can use this:

SELECT COUNT(*) FROM Table1 WHERE INSTR(Table1.tags, '\' rock') > 0;

fiddle

edited Mar 14 '14 at 07:56

answered Mar 14 '14 at 07:47

G one

2,679
2
14
18

I have used this query but returned this type of records also [u'punk', u' punk rock', u' pop punk', u" rock'n'roll"] and [u'indie', u' rock app', u' alternative', u' emo'] – GOOG Mar 17 '14 at 06:50
@GOOG: so it should only be for `rock`?? – G one Mar 17 '14 at 06:57
yes. it will have some cases(quotes) like Case 1:[u'under 2000 listeners', u' rock', u' brazil', u' brasil'] case 2: [u'noise rock', u' kill rock stars', u' metal', u'rock'] case 3: [u'punk', u' punk rock', u' pop punk', u" rock"] – GOOG Mar 17 '14 at 07:22
case 1 is single quotes with space then rock, case 2 is single quotes without space then rock and case 3 is double quotes with space and rock also without space also possible. these type of records only consider to select. – GOOG Mar 17 '14 at 07:24

mysql select count of records from a string which have exact substring

4 Answers4