Skip to content
Advertisement

return the Nth word from database

I want to get nth word from a column I’m using a code line and it works for me but there is an issue, for example:

First line is: “the Nth word from database”

Second line is: “return the Nth word from database and more words”

When I search for 6th word ‘database’ it returns my first line and second line but I don’t want to get my first line because it has only 5 words. thank you all

My code line:

SELECT *,
       SUBSTRING_INDEX(SUBSTRING_INDEX(`Text`, ' ', 6), ' ', -1) as Nth 
FROM `tbl_name`

Answer

Having six words in you sentence means that you have to have at least five spaces, adding simlpe condition will resolve your problem:

select *,
       case when length(`text`) - length(replace(`text`, ' ', '')) >= 5 then
           substring_index(replace(`text`, substring_index(`text`, ' ', 5) , ''), ' ', 2)
       else null end Nth
from `tbl_name`

Also I changed your query, because it didn’t take into account that you might not have 6th space (exactly six words).

Demo

Or even more concicse:

select *,
       substring_index(substring_index(`text`, ' ', 5 - (length(`text`) - length(replace(sentence`text` ' ', ''))) - 1), ' ', 1)
from `tbl_name`

Another demo.