-6

How to find number of occurrence of a specific value in table column which contain pipe delimiter in MySQL query?

Please refer the below string:

0|5573|73|5573|73

I want the count of 73 in above string it should give answer as 2.

-----------------------------
column            -- count
------------------------------
0|5573|73|5573|73 -- 2
juzraai
  • 5,693
  • 8
  • 33
  • 47
Ranjit Gange
  • 11
  • 1
  • 1
    Welcome to Stack Overflow! Please take the [tour], have a look around, and read through the [help], in particular [*How do I ask a good question?*](/help/how-to-ask) – T.J. Crowder Sep 01 '17 at 07:47
  • Possible duplicate of [Count the number of occurrences of a string in a VARCHAR field?](https://stackoverflow.com/questions/12344795/count-the-number-of-occurrences-of-a-string-in-a-varchar-field) – juzraai Sep 01 '17 at 07:53
  • Good thing is, that this doesn't look like a new voting ring when two accounts were created almost at the same time, never did anything for the same amount of time and are coming from the same place. And that answer obviously wasn't propared and arranged with OP prior asking this question. – Tom Sep 01 '17 at 08:07

1 Answers1

2

Please refer below mysql query which will return no of occurence of specific value as per your question.

In below query test_table is a table which contain the column named 'test_colum' which contains values like '0|5573|73|5573|73' and you want to find the no of occurrences of value '73' in it.

SELECT 
    test_colum,  
(IF((POSITION('73|' IN test_colum))=1,1,0))
+
  ( ROUND (   
        (
            (LENGTH(test_colum)
            - LENGTH( REPLACE ( test_colum, "|73|", "")) 
            ) 
        ) / LENGTH("|73|")        
    ) 
+
IF((substring_index(test_colum,'|',-1) = '73'),1,0)) AS value_cnt
FROM test_table;

It will produce result as following : 

test_colum         value_cnt
0|5573|73|5573|73    2
Priti Tingri
  • 36
  • 3