sql - Sort Postcode for menu/list -


I have to sort a list of UK postcodes to order.

Is there an easy way

UK postcodes are made up of letters and numbers:

View complete format information:

But my The problem is this is a simple alpha sort does not work because each code starts with 1 or two letters and then immediately numbers one, two digits, then one place is on the second and a letter then. Like LS1 1A or LS281AA, there is another case, where once the number is greater than 99 in the first section, then this continues to be 9a etc.

The reason for alpha sort 10 is 1:

Pre> ... LS1 9ZZ LS10 1AA ... LS2

I have a SQL function To create a printable postcode, a sortable postcode such as 'LS1 9ZZ' will become 'LS01 9ZZ', then use this function in sequence by the segment.

Has anyone done or something similar in the past?

You need to think of it as a tokening problem so that SW1A 1AA should notify:

  • 1
  • AAA

    • (However you can break the incoming part in 1 more AA

    • P> Once you break the postcode in those component parts, the sorting should be quite easy. GIR 0AA is an exception with postcode but you can strictly test one for that

    Edit: Some more ideas on tokening

    These are valid postcode formats (source: Royal Mail PAF User Guide page 8 - Link below):

    AN NAA
    NAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA - AAA NAA ( Only for GIR 0 AAA codes) ANNAAAAAAAAAAAAA

    Therefore some algorithms (assuming we want to separate area and unit postcode):

    • Code = gir 0 aa? Tokenize for GI / R / 0 / AA (treating R to simplify things in the district)
    • Length codes of 5 characters, e.g. G1 3 AF? Tokenize for G / 1/3 / AF
    • A 6-character code with 3 characters, for example W1P 1HQ? Tokenize to W / 1 / P / 1 / headquarters
    • The code is a letter with the second letter, with 6 characters, for example CR2 6 XH? Tokenised for CR / 2/6 / XH
    • A code of 7 characters with 4 letters, for example a letter EC1A 1BB? Tokens to EC / 1 / A / 1 / BB
    • Otherwise token for example TW14 2ZZ, TW / 14/2 / ZZ

    If the user chooses To display a list of postcodes, I will follow Neil Butterworth's suggestion of the 'Sortable' version of the postcode in the database

    • Two letters (if the right pad is small) for the field,
    • The easiest way is to create a sorted version for two types of districts:
    • The area
    • For the area
    • > Two for the unit

    And the GIR 0AA is a little exception again To If you pad with spaces, the sort order should be correct. Example: To represent # space example:

    • W1 # 1AA => W ## 1 ## 1A
    • WC1 # 1AA => WC # 1 ## 1AA
    • W10 # 1AA => W # 10 ## 1A
    • W1W # 1AA => W ## 1W # 1A
    • GIR # 0AA => GI # R # # AA
    • WC10 # 1AA => WC10 ## 1AA
    • WC1W # 1AA => WC # 1W # 1AA
      • < / Ul>

      You need to correct - the padd area if it is too small: left-padding produces wrong sequence order. B., E, G, L, M, N, S, W - All the single letter areas will be sorted before all the two-letter areas - AB, AL, ..., Z. If you are left-padded

      Natural W1, W2, ..., W. 9, W. 10 order is up

    To make sure the district number should be left.

    Comments

    Post a Comment

    Popular posts from this blog

    python - Overriding the save method in Django ModelForm -

    I'm having trouble overriding a ModelForm save method. I get this error I'm: Exception type: TypeErier Exception Value: Save () found an unexpected keyword argument 'committed' My intentions include a form 3 fields Submit several values ​​for, then create an object for each combination of those areas, and to save each object file models.py class collarsult type (models.Model): id = model. AutoField (db_column = 'icontact_result_code_type_id', primary_key = true) callResult = models.ForeignKey ( 'CallResult', db_column = 'icontact_result_code_id') campaign = models.ForeignKey ( 'campaign', db_column = 'icampaign_id') CALLTYPE = models.ForeignKey ( 'CALLTYPE', db_column = 'icall_type_id') agent = models.BooleanField (db_column = 'bagent', default = true) teamLeader = models.BooleanField (db_column = 'bTeamLeader', default = true) active = models.BooleanField (db_column = Django.form...
    Read more

    html - CSS autoheight, but fit content to height of div -

    I have a div in which there are three children- div in the left and middle div: float: left while true: float: true because it is my layout Actually will spoil, I've used ClearFix Hack: .cf {zoom: 1; }. Cf: First,. Cf: {content: ""; Display: Table; }. Cf: After {clear: both; } It still works, but I want to be the right div an indicator. So it should fill 100% of the parent unit of height. How can I complete this? PS is the full code: & Lt; / Div & gt; & Lt; Div class = "mobile_content" & gt; & Lt; H5 & gt; {{Data.titel}} & lt; / H5> & Lt; Table & gt; & Lt; TR & gt; & Lt; Th & gt; Komponist: & lt; / Th & gt; & Lt; TD & gt; {{Data.komponist}} & lt; / TD & gt; & Lt; / TR & gt; & Lt; TR & gt; & Lt; Th & gt; Instrument: & lt; / Th & gt; & Lt; TD & gt; {{Data.instrumente}} & lt; / TD & gt; & Lt; / TR & gt;...
    Read more

    qt - How to prevent QAudioInput from automatically boosting the master volume to 100%? -

    I have been trying to use Qt5 multimedia to record audio with QAudioInput, however, when I see If QAudioInput is started, it increases the master volume of my sound device to 100%. How can I prevent QAudioInput from changing the master volume? My current development platform is Linux with PalsAdio (with flat audio disabled). How can I use QAudioInput : QAudioDeviceInfo device_info = QAudioDeviceInfo :: defaultInputDevice (); QAudio format format; Format.setSampleRate (44100); Format.setChannelCount (1); Format.setSampleSize (16); Format.setCodec ("Audio / PCM"); Format.setSampleType (QAudioFormat :: SignedInt); Format.setByteOrder (QAudioFormat :: LittleEndian); Std :: cout & lt; & Lt; Device_info.deviceName (). ToUtf8 (). ConstData () & lt; & Lt; Std :: endl; QAudioInput * default_device = new QAudioInput (device_info, format); QIODevice * default_io_device = default_device-> Start (); a QAudioInput.setVolume () method is not you I ...
    Read more