CODEWITHSUNDEEP
mysqlsql
al404IT
al404IT

Reputation: 1418

mysql query where second words starts with a letter

I have a table with:

Name Surname and Outputname ( = Name Surname )

Name and Surname should always have a value, but some time are empty. I need to select from table by surname and i can't use surname field. I can't even change field values with a script because i get this table from an outside source and can be change any time. Database is MySQL 4.x

Can i select by second word in Outputname starting with some letter? something like

SELECT Outputname FROM USERS 
WHERE seconword(Outputname) LIKE 'A%' SORT BY seconword(Outputname) ASC

Upvotes: 2

Views: 3023

Answers (3)

echo_Me
echo_Me

Reputation: 37233

try this

   SELECT 
   SUBSTRING_INDEX(Outputname, ' ', -1) as SecondWord
   FROM USERS 
   WHERE SUBSTRING_INDEX(Outputname, ' ', -1) LIKE 'A%'
   ORDER BY SecondWord ASC

demo

Upvotes: 2

alcoceba
alcoceba

Reputation: 423

I didn't understand your question at all. So you only have access to Outputname (composed by two words name+surname) and you have to sort it by the second word right?

Try something like (it worked for me):

SELECT 
    Outputname, 
    SUBSTRING_INDEX(Outputname, ' ', -1) as SecondWord
FROM USERS 
ORDER BY SecondWord ASC

Clause SUBSTRING_INDEX(Outputname, ' ', -1) as SecondWord returns the last word placed after a space. So If you have Outputname = 'Maria Callas' it returns 'Callas', if you have Outputname = 'Sophia Cecilia Kalos' it returns 'Kalos'.

Upvotes: 0

raina77ow
raina77ow

Reputation: 106385

One possible approach:

    SELECT Surname 
      FROM (
       SELECT SUBSTRING_INDEX(Outputname, ' ', -1) 
              AS Surname
         FROM Users) AS S
    WHERE Surname LIKE 'A%' 
 ORDER BY Surname;

SQL Fiddle. This method is based on assumption that Outputname's format is always 'FirstName LastName' (i.e., ' ' symbol is used as a delimiter, and used only once each time).

Upvotes: 1

Related Questions