Custom SQL sort by

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 开发者_StackOverflow中文版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

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

You're on the right track, trimming the field down to its first four characters:

ORDER BY FIELD(LEFT(postcode, 4), 'RG20', 'RG14', ...),
--          or SUBSTRING(postcode FROM 1 FOR 4)
--          or SUBSTR(postcode, 1, 4)

Here you don't want DESC.

(If your result set contains postcodes whose prefixes do not appear in your FIELD() ordering list, you'll have a bit more work to do, since those records will otherwise appear before any explicitly ordered records you specify. Before 'RG20' in the example above.)

If you want a completely custom sorting scheme, then I only see one way to do it... Create a table to hold the values upon which to sort, and include a "sequence" or "sort_order" field. You can then join to this table and sort by the sequence field.

One note on the sequence field. It makes sense to create it as an int as... well, sequences are often ints :) If there is any possibility of changing the sort order, you may want to consider making it alpha numeric... It is a lot easier to insert "5A" between "5 and "6" than it is to insert a number into a sequence of integers.

Another method I use is utilising the charindex function:

order by charindex(substr(postcode,4,1),"RG20RG14RG18...",1)

I think that's the syntax anyway, I'm just doing this in SAS at the moment so I've had to adapt from memory!
But essentially the sooner you hit your desired part of the string, the higher the rank.

If you're trying to rank on a large variety of postcodes then a case statement gets pretty hefty.