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

Question

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.

Answer 1

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?