Custom SQL sort by

Question

Use: The user searches for a partial postcode such as 'RG20' which should then be displayed in a specific order. The query uses the MATCH AGAINST method in boolean mode where an example of the postcode in the database would be 'RG20 7TT' so it is able to find it. At the same time it also matches against a list of other postcodes which are in it's radius (which is a separate query).

I can't seem to find a way to order by a partial match, e.g.:

ORDER BY FIELD(postcode, 'RG20', 'RG14', 'RG18','RG17','RG28','OX12','OX11') 
      DESC, city DESC

Because it's not specifically looking for RG20 7TT, I don't think it can make a partial match.

I have tried SUBSTR (postcode, -4) and looked into left and right, but I haven't had any success using 'by field' and could not find another route... Sorry this is a bit long winded, but I'm in a bit of a bind. A UK postcode splits into 2 parts, the last section always being 3 characters and within my database there is a space between the two if that helps at all.

Although there is a DESC after the postcodes, I do need them to display in THAT particular order (RG20, RG14 then RG18 etc..) I'm unsure if specifying descending will remove the ordering or not

Answer 1

Order By Case
            When postcode Like 'RG20%' Then 1
            When postcode Like 'RG14%' Then 2
            When postcode Like 'RG18%' Then 3
            When postcode Like 'RG17%' Then 4
            When postcode Like 'RG28%' Then 5
            When postcode Like 'OX12%' Then 6
            When postcode Like 'OX11%' Then 7
            Else 99
            End Asc
    , City Desc