counting unexpected Chars when trying to find occurrence of the words in phpmyadmin

I have a table that contains different length of sentences from one word to 40 words, and I want to count each word separately and how many times it occurred in that table, but whenever the sentence is containing just one word it prints unexpected Char. for some reasons any ideas why ?

this is a demo of my DB

DB demo

and this is the code

create table messages(sent varchar(200), verif int);
insert into messages values


             ('HI' , null),

             ('HI alex how are you' , null),

             ('bye' , null);

select * from messages;

UPDATE messages set sent = TRIM(sent);
UPDATE messages set sent = REGEXP_REPLACE(sent,' +',' ')

with recursive cte as (
    select 
        substring(concat(sent, ' '), 1, locate(' ', sent)) word,
        substring(concat(sent, ' '), locate(' ', sent) + 1) sent
    from messages
    union all
    select 
        substring(sent, 1, locate(' ', sent)) word,
        substring(sent, locate(' ', sent) + 1) sent
    from cte
    where locate(' ', sent) > 0
)
select row_number() over(order by count(*) desc, word) wid, word, count(*) freq
from cte 
group by word
order by wid

out put of the code 


wid word    freq
1       2
2   HI  2
3   alex    1
4   are     1
5   bye     1
6   how     1
7   you     1


expected output 
wid word    freq
1   HI  2
2   alex    1
3   are     1
4   bye     1
5   how     1
6   you     1


Answer

Your problem is in these lines:

substring(concat(sent, ' '), 1, locate(' ', sent)) word,
substring(concat(sent, ' '), locate(' ', sent) + 1) sent

When sent doesn’t contain a space, locate(' ', sent) returns 0 and substring returns an empty string, which is what you are seeing counted in your output. To fix that, use concat(sent, ' ') in place of sent:

substring(concat(sent, ' '), 1, locate(' ', concat(sent, ' '))) word,
substring(concat(sent, ' '), locate(' ', concat(sent, ' ')) + 1) sent

For your sample data this gives:

wid     word    freq
1       HI      2
2       alex    1
3       are     1
4       bye     1
5       how     1
6       you     1

Demo on dbfiddle

Source: stackoverflow
The answers/resolutions are collected from stackoverflow, are licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0 .