搜索列中每个空格分隔值的字符串

Suppose I have a column named CustomerNames in database with values:

  1. Vins et alcools Chevalier
  2. Magazzini Alimentari Riuniti
  3. Hungry Owl All-Night Grocers
  4. Split Rail Beer & Ale
  5. Alfreds Futterkiste

I want to make a query which will search only for the first letters of FIRSTNAME and LASTNAME, and list the names in ASC order based on FIRSTNAME then LASTNAME. for example if I search for "al" in above table, it should return me something like below:

  1. Alfreds Futterkiste
  2. Vins et alcools Chevalier
  3. Split Rail Beer & Ale
  4. Magazzini Alimentari Riuniti
  5. Hungry Owl All-Night Grocers

I have used below query.

SELECT * FROM Customers where CustomerName LIKE 'al%' OR CustomerName LIKE '% al%' ORDER BY CustomerName

Output:

  1. Alfreds Futterkiste
  2. Hungry Owl All-Night Grocers
  3. Magazzini Alimentari Riuniti
  4. Split Rail Beer & Ale
  5. Vins et alcools Chevalier

Which is ordering results only on the basis of firstnames. But I want a query which gives output using firstnames and then lastnames. Thanks.

I dont think there's a straight forward answer to that. You need to write a function or store procedure to order results based on a column-values-that-is-space-separated

Have a look at this MySQL: how to sort the words in a string using a stored function?

Try this one:

SELECT * FROM Customers
where CustomerName LIKE 'al%' 
OR CustomerName LIKE '% al%' 
ORDER BY substring(CustomerName, instr(CustomerName, 'al'))