How do you order by a search phrase found in the beginning of a word and only afterwards the middle using SQL.

Question

Let's say I have table [users] with the field [Name] and I've written a query in my application to search by name.

The names in the database are:

Lala Ally

My SQL query is:

SELECT * FROM users WHERE [Name] LIKE '%al%'

where 'al' is my search term.

How would I sort the results by search term found in the beginning i.e. '%al' and only afterwards '%al%'.

The result set should be

Ally Lala

I'm using SQL SERVER.

Thanks.

Answer 1

Something like this:

SELECT 0, [Name] FROM users WHERE [Name] LIKE 'al%'

UNION

SELECT 1, [Name] FROM users WHERE [Name] LIKE '%al%'
  AND NOT [Name] LIKE 'al%'

ORDER BY
  1, 2

Answer 2

If your DBMS supports it, I guess you could wrap your select in another select that uses index_of on the name, such as:

select index_of('al', Name) as sortorder, * from (select * FROM users WHERE [Name] LIKE '%al%')
order by sortorder

Answer 3

Try this. This will work for all strings as you require assuming you are using sql server-

SELECT * FROM users WHERE [Name] LIKE '%al%'
ORDER BY PATINDEX('%al%',[Name])