CODEWITHSUNDEEP
mysqllocate
user3649739
user3649739

Reputation: 1869

Select Two Words before character in string in mysql

I can find a given character using locate:

Select Locate(':','bla bla bla Favorite Color: Red yada yada')

But would like to select (at least) the word before it so in the above query I'd like to return 'Color'.

Ideally I'd love to return up to X Words where they are all init cap so in this case 'Favorite Color'. I'm assuming I'd need to add Binary and Regex for that but I can do so if I understand the basic structure of how to extract the word or words before the located character.

Upvotes: 1

Views: 619

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

If you just need to select the word just before : and the words separated by a space then you can use substring_index as 

select 
 substring_index(
   substring_index('bla bla bla Favorite Color: Red yada yada',':',1)
   ,' ',-1
 ) as w ;

And if you want Favorite Color then change the index from -1 to -2

Upvotes: 1

Related Questions