CODEWITHSUNDEEP
phpmysqlregexstring
Trimantra Software Solution
Trimantra Software Solution

Reputation: 3329

How to count no. of exact keyword occurance in one column in mysql

I'm looking for the solution to get no. of exact keyword occurrence in mysql column.

As this is needed for "multiple keyword search" functionality, there can be single or multiple keyword.

I found very good solution as below, but it count for no of containing keyword rather than count for Exact keyword.

SELECT 
in_member_id,
st_jsondata,    
ROUND (   
    (
        LENGTH(st_jsondata)
        - LENGTH( REPLACE ( st_jsondata, "John", "") ) 
    ) / LENGTH("John")        
)
+
ROUND (   
    (
        LENGTH(st_jsondata)
        - LENGTH( REPLACE ( st_jsondata, "Seipelo", "") ) 
    ) / LENGTH("Seipelo")        
)
AS count1 FROM tbl_member_master Having count1 > 0 ORDER BY count1 DESC LIMIT 0, 10

What is problem with solution is it search for "John", "John123", JohnAb" etc. same for second keyword. I don't expect this way.

If I will get something like regular expression replace function, it worked for me.

Please do share if anybody having something related to this problem.

Search Keyword: sonal, khunt

Column Data:
ID | ColumnToSearch
1 | {'st_first_name':'sonal','st_last_name':'khunt'}
2 | {'st_first_name':'sonal105','st_last_name':'khunt105'}
3 | {'st_first_name':'sonal109','st_last_name':'khunt109'}
4 | {'st_first_name':'sonal','st_last_name':'patel'}


Expected Result:
ID | Count
1 | 2
2 | 0
3 | 0
4 | 1

Thanks.

Upvotes: 1

Views: 88

Answers (2)

Rick James
Rick James

Reputation: 142298

If you are guaranteed to have the apostrophes around the strings, then 'khunt' instead of khunt in the 3 LENGTH calls.

Upvotes: 0

karthik manchala
karthik manchala

Reputation: 13640

You can add where filter using word boundaries of REGEXP to your query:

SELECT 
in_member_id,
st_jsondata,    
ROUND (   
    (
        LENGTH(st_jsondata)
        - LENGTH( REPLACE ( st_jsondata, "John", "") ) 
    ) / LENGTH("John")        
)
+
ROUND (   
    (
        LENGTH(st_jsondata)
        - LENGTH( REPLACE ( st_jsondata, "Seipelo", "") ) 
    ) / LENGTH("Seipelo")        
)
AS count1 FROM tbl_member_master WHERE st_jsondata REGEXP '[[:<:]](John|Seipelo)[[:>:]]' Having count1 > 0 ORDER BY count1 DESC LIMIT 0, 10

This will only find those records with strings like John than John123 and replaces.. so that you get the exact count.

Upvotes: 1

Related Questions