Finding number of occurence of a specific string in MYSQL

Question

Consider the string "55,33,255,66,55"

I am finding ways to count number of occurence of a specific characters ("55" in this case) in this string using mysql select query.

Currently i am using the below logic to count

 
select CAST((LENGTH("55,33,255,66,55") - LENGTH(REPLACE("55,33,255,66,55", "55", ""))) / LENGTH("55") AS UNSIGNED)

But the issue with this one is, it counts all occurence of 55 and the result is = 3, but the desired output is = 2.

Is there any way i can make this work correct? please suggest.

NOTE : "55" is the input we are giving and consider the value "55,33,255,66,55" is from a database field.

Regards,

Balan

Bohemian · Accepted Answer · 2012-02-27 11:48:19Z

6

You want to match on ',55,', but there's the first and last position to worry about. You can use the trick of adding commas to the frot and back of the input to get around that:

select LENGTH('55,33,255,66,55') + 2 -
    LENGTH(REPLACE(CONCAT(',', '55,33,255,66,55', ','), ',55,', 'xxx'))

Returns 2

I've used CONCAT to pre- and post-pend the commas (rather than adding a literal into the text) because I assume you'll be using this on a column not a literal.

Note also these improvements:

Removal of the cast - it is already numeric
By replacing with a string one less in length (ie ',55,' length 4 to 'xxx' length 3), the result doesn't need to be divided - it's already the correct result
2 is added to the length because of the two commas added front and back (no need to use CONCAT to calculate the pre-replace length)

edited Feb 27, 2012 at 11:48

answered Feb 27, 2012 at 11:42

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

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

Comments

rMX · Accepted Answer · 2012-02-27 11:47:40Z

0

Try this:

select CAST((LENGTH("55,33,255,66,55") + 2 - LENGTH(REPLACE(concat(",","55,33,255,66,55",","), ",55,", ",,"))) / LENGTH("55") AS UNSIGNED)

edited Feb 27, 2012 at 11:47

answered Feb 27, 2012 at 11:41

rMX

1,09016 silver badges23 bronze badges

Comments

animuson · Accepted Answer · 2012-02-28 02:14:49Z

0

I would do an sub select in this sub select I would replace every 255 with some other unique signs and them count the new signs and the standing 55's.

If(row = '255') then '1337'

for example.

edited Feb 28, 2012 at 2:14

animuson♦

54.9k28 gold badges142 silver badges150 bronze badges

answered Feb 27, 2012 at 11:34

noonecares

3161 silver badge6 bronze badges

Collectives™ on Stack Overflow

Finding number of occurence of a specific string in MYSQL

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related