help with a select

[letimati]

Hi I have a table with peoples names in it like so.

table
name
--------
John Smith
Adam J Simpson
Simon
Alex 3rd of kingdom 2 Smith

I want to select all the rows where the last word starts with 'S' i.e. an alphabet search on the last name.

I was trying to use somthing like

WHERE name REGEXP '[[:<:]]s[[:alnum:]]'

which i think means a word starting with 's' which only has letters after it.

Basically my thinking is find a word tht starts with a 's' that has no spaces after it, which would mean thats the last word.

I am going a few other trys but thought i would post in case this is in fact easy.

thanks in advance for any time taken by you.

 

[LKBrwnDBA]

Try:

SELECT ...
 WHERE SUBSTR(NAME, INSTR(NAME,' ',-1)+1,1) = 'S'

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[letimati]

Just gives a error message?

 

[letimati]

This works and also lets you order on the last work as well


WHERE REVERSE( SUBSTRING_INDEX( REVERSE( name ) , ' ', 1 ) ) LIKE 'S%'
ORDER BY REVERSE( SUBSTRING_INDEX( REVERSE( name ) , ' ', 1 ) )

 

[LKBrwnDBA]

Sorry I gave you the "Oracle" version.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[letimati]

No problem it got me looking in the right direction anyway so thanks