Custom SQL Sort

Question

I am new to the forum and have not programmed for a long time, so please tell me if this was the wrong approach method.

Usage: The user searches for a partial zip code, such as "RG20", which then needs to be displayed in a specific order. The query uses the MATCH AGAINST method in boolean mode, where an example of a zip code in a database is “RG20 7TT” so that it can find it. At the same time, it also matches the list of other postal codes that are in it with a radius (which is a separate request).

Problem: I cannot find a way to sort by partial match, for example:

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

Since he is not specifically looking for the RG20 7TT, I do not think he can make a partial match. I tried SUBSTR (zip code, -4) and looked left and right, but I did not manage to use the "field" and could not find another route ... Sorry, this is a little longer, but I'm a little bit attached. The UK zip code is broken into 2 parts, the last section is always 3 characters, and in my database there is a space between them, if that helps at all. Thank you in advance.

EDIT: although there is DESC after the zip codes, I need them to appear in that particular order (RG20, RG14, then RG18, etc.). I am not sure if specifying downstream remove the order or not.

Answer 1

You are on the right track by trimming the field 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 do not want DESC.

(If your result set contains zip codes whose prefixes do not appear in the FIELD () order list, you will have to work a bit, as these records will be displayed differently than any explicitly ordered records that you specified. RG20 'in the above above example.)

Answer 2

 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 

Answer 3

If you want to fully customize the sorting scheme, then I see only one way to do this ... Create a table to store the values ​​for sorting and specify the "sequence" or "sort_order" field. Then you can join this table and sort by sequence field.

One note on the sequence box. It makes sense to create it as an int like ... well, sequences are often ints :) If there is any possibility of changing the sort order, you might want to make it an alphabetic number ... It is much easier to insert "5A" between "5 and" 6 than this is for inserting a number into a sequence of integers.

Answer 4

Another method I use is the charindex function :

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

I think the syntax is anyway, I'm just doing it in SAS for now, so I had to adapt from memory!
But, in fact, the sooner you click on the desired part of the line, the higher the rank.

If you are trying to rank on a large number of zip codes, then the case argument becomes pretty impressive.