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

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.

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])

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

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

Just ended up with this query variant for SQLite doing same, search term is "jeff":

SELECT name
 FROM details WHERE name LIKE 'jeff%' OR name LIKE '%jeff%' 
    ORDER BY (0 - (name LIKE 'jeff%') + (name LIKE '%jeff%')) || name 
    LIMIT 100;