1

I have a table with three column say: name,flag, and value, where name is a primary key of type string, flag contains integer, and value contains a LONGTEXT.

The LONGTEXT contains strings which may contains a word that can appear multiple times.

I know that I can search for specific string using LIKE. But I want to query the rows where a specific word say my-word appear more than once, and I want to know how many times it appeard (2,3, etc.) in each cell?

Is this possible?

None
  • 281
  • 1
  • 6
  • 16
  • 1
    use replace() to remove the word from the longtext. then subtract the length of the original from the one with the word removed and divide by the number of characters in the word. – xQbert Jul 15 '19 at 18:02
  • (@xQbert - Make your Comment into an Answer.) – Rick James Jul 15 '19 at 22:08
  • @xQbert Can you plz check my answer? If correct, can you comment and up vote if you like :) – None Jul 16 '19 at 08:32
  • 1
    @RickJames Normally I would but I was in a hurry and didn't actually write up a valid/complete answer. So instead my comment was intended to provide a possible direction to consider. I didn't feel it was a complete answer just a good idea to try. I'm also really happy that none was able to take the idea and build it themself! – xQbert Jul 16 '19 at 12:54

1 Answers1

2

The query should be like this:

select value, (length(value)-length(replace(value,'my-word','')))/7
from mytable
where value like '%my-word%'

I divide by 7 because the word my-word contains 7 characters. So the number you divide by should be the number of characters of the word you want to count how many times it appeared.

None
  • 281
  • 1
  • 6
  • 16
  • My only comment would be to use a variable instead of hard coding the value and the length of 7. Or alternatively; use a procedure and pass in a paramater. Possibilities defined here: https://stackoverflow.com/questions/11754781/how-to-declare-a-variable-in-mysql – xQbert Jul 16 '19 at 12:51
  • I'd also alias the calculated column for easier reference. `WordOccurances` or something. – xQbert Jul 16 '19 at 14:23
  • 1
    Or simply change `/7` by `/length('my-word')` – Rick James Jul 16 '19 at 14:25