CODEWITHSUNDEEP
mysql
balanv
balanv

Reputation: 10888

Finding number of occurence of a specific string in MYSQL

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

Upvotes: 3

Views: 239

Answers (3)

noonecares
noonecares

Reputation: 216

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.

Upvotes: 0

Bohemian
Bohemian

Reputation: 424973

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)

Upvotes: 6

rMX
rMX

Reputation: 1090

Try this:

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

Upvotes: 0

Related Questions