How to sort based on keyword search?

My sql statement

SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))

How do I list the top matched result like this

Green
Army Green
Hunter Green

instead of the below

Army Green
Green
Hunter Green

I know that we can't use the order by asc or desc with my case.

I plan to add a space before 'green' and use the dictionary object or insert into the database and sort it alpha order. I think there is a better technique out here that can do the job instead of using my slow technique specially dealing with more than multi-million records in the keyword database.

By the way, we are still using the ancient SQL Server 2000.

this is the keywords which is related to green. I like to get the top matched result based on the input keyword. The result should show the closest match to the keyword.

Data in the database

Army Green
Blue-Green
Bright Green
British Racing Green
Camouflage Green
Dark Green
Dark Pa开发者_JS百科stel Green
Electric Green
Fern Green
Forest Green
Green
Green-Yellow
Hunter Green
India Green
Islamic Green
Jungle Green
Kelly Green
Lawn Green
Lime Green
Moss Green
MSU Green
Office Green
Pastel Green
Persian Green
Pine Green
Sea Green
Shamrock Green
Spring Green
Tea Green
Yellow-Green

Perhaps...

SELECT *  
    FROM mytable 
    WHERE (CONTAINS(keyword,' "green" '))
    ORDER BY FIND_IN_SET('green', keyword);

Find_in_set MySQL though; you may not have it. It just returns the position of the word 'Green' in the keyword column.

SQL Server appears to have CHARINDEX('green', keyword) if that's any help.

Well, you can special-case exact matches:

SELECT *  
  FROM mytable 
 WHERE keyword = 'green'
UNION ALL
SELECT *  
  FROM mytable 
 WHERE (CONTAINS(keyword,' "green" '))
   AND keyword <> 'green'

I'm not sure exactly what you're after, and I'm not very familiar with full text search, but perhaps you could just order by len(keyword).

That would work for your example, anyway. It wouldn't prioritize lines with more than one instance of "green" though.