CODEWITHSUNDEEP
phpsql
user4565864
user4565864

Reputation:

SQL for searching username efficiently

I have a table with userID, userFirstName, userLastName as fields and lots of records in it. I have to list all users similar to the key value received from user. I have it as $searchKey in my program.

If the user searches with a string namely "Robert" ,
the result should give related values to Robert first,
then to Rober ,
then to Robe ,
then Rob,
then Ro ,
and finally R.

Upvotes: 2

Views: 155

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One way you can do this is with a case statement in the order by:

order by (case where col like '%Robert%' then 1
               where col like '%Rober%' then 2
               where col like '%Robe%' then 3
               where col like '%Rob%' then 4
               where col like '%Ro%' then 5
               where col like '%R%' then 6
          end)

Upvotes: 2

Related Questions