Get best hit first

I have a table department with a columns SearchKeysName and BOLD_ID. Bold_ID is just a number for identification of row. It contains data like this:

Bold_ID; SearchKeysName
1005; [12212][FALKENBERG][32-1][][523451]
1000; [124132][AB CD BYGG][GÖTEBORG][124132-1][][CD-BYGG CO][556435979101]

To search data I have SQL like this:

SELECT DISTINCT TOP 100 BOLD_ID FROM Department UPPER(SearchKeysName) LIKE '%[%32-1%]%' ESCAPE ''

In this case I searched for 32-1 so it will pick the rows above. What I want is to make sure the exact hit is placed on top of the result. Like this

1000
1005

This is of course a simplified example. In reality there are thousands of rows so I can miss the first line with 32-1 as there are many others that and with this string when search string is short.

Only idea I have so far is to make 2 searches. One with

LIKE '%[32-1]%'

If that don’t find anything then try more generic like above.

EDIT Now tried this:

SELECT distinct TOP 100 DEPARTMENT.BOLD_ID
from Department 
where upper(SearchKeysName) like '%[%32-1%]%' ESCAPE ''
order by case when SearchKeysName like '%[32-1]%' ESCAPE '' then 0 else 1 end

And got this

Msg 145, Level 15, State 1, Line 1
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.

Answer

Use a case expression to get exact hit rows first:

order by case when SearchKeysName like '%[32-1]%' ESCAPE '' then 0 else 1 end, Bold_ID

To get distinct BOLD_ID’s, do a GROUP BY, to sort a BOLD_ID with an exact match first (even if it also has a non-exact match):

SELECT TOP 100 BOLD_ID
from Department 
where SearchKeysName like '%[%32-1%]%' ESCAPE ''
group by BOLD_ID
order by min(case when SearchKeysName like '%[32-1]%' ESCAPE '' then 0 else 1 end),
         BOLD_ID