Results 1 to 5 of 5

Thread: UK postcodes

  1. #1
    Join Date
    Sep 2009
    Posts
    19

    Unanswered: UK postcodes

    Hi I have a table with a list of uk zip/post codes like:

    SW185NT
    SW18 7RT
    SW15XQ
    NW2 6TT
    SW18 5NT
    SW15NT
    NW1 5NT

    They fall into four categories:

    3Chars-Space-3Chars
    4Chars-Space-3Chars
    3Chars-3Chars
    4Chars-3Chars

    Basically I need to break these into part post codes (first 3/4chars) and count volumes and values etc... So I need:

    Poscode | Count
    NW1 | 1
    SW1 | 1
    SW18 | 3
    SW15 | 2
    NW2 | 1

    Is there a way to do this in a single select statement?

    Sorry that this is such a basic question... I'm a bit of a novice!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is this any good?
    Code:
    DECLARE @addresses table (
       post_code char(8)
    )
    
    INSERT INTO @addresses (post_code) VALUES ('SW185NT')
    INSERT INTO @addresses (post_code) VALUES ('SW18 7RT')
    INSERT INTO @addresses (post_code) VALUES ('SW15XQ')
    INSERT INTO @addresses (post_code) VALUES ('NW2 6TT')
    INSERT INTO @addresses (post_code) VALUES ('SW18 5NT')
    INSERT INTO @addresses (post_code) VALUES ('SW15NT')
    INSERT INTO @addresses (post_code) VALUES ('NW1 5NT')
    INSERT INTO @addresses (post_code) VALUES ('INVALID')
    INSERT INTO @addresses (post_code) VALUES ('MEH')
    
    --Pattern matching method
    SELECT Left(trimmed_post_code, place_to_trim) As voila
    FROM   (
            SELECT trimmed_post_code
                 , CASE WHEN trimmed_post_code LIKE '[A-Z][A-Z][0-9][0-9][A-Z][A-Z]' THEN
                     3
                   ELSE
                     CASE WHEN trimmed_post_code LIKE '[A-Z][A-Z][0-9][0-9][0-9][A-Z][A-Z]' THEN
                       4
                     ELSE
                       8 --i.e. whole string
                     END
                   END As place_to_trim
            FROM   (
                    SELECT Replace(post_code, ' ', '') As trimmed_post_code
                    FROM   @addresses
                   ) As x
           ) As y
    
    --Alternative
    SELECT CASE WHEN Len(trimmed_post_code) = 7 THEN Left(trimmed_post_code, 3) ELSE Left(trimmed_post_code, 4) END As voila
    FROM   (
            SELECT Replace(post_code, ' ', '') As trimmed_post_code
            FROM   @addresses
           ) As x
    --Exclude invalid formats
    WHERE  ( trimmed_post_code LIKE '[A-Z][A-Z][0-9][0-9][A-Z][A-Z]'
          OR trimmed_post_code LIKE '[A-Z][A-Z][0-9][0-9][0-9][A-Z][A-Z]' )
    George
    Home | Blog

  3. #3
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    zedordead, If the pattern is to remove the last 3 characters (plus any space that may be left), you can try this:

    Code:
    SELECT RTRIM(SUBSTRING(POST_CODE,1,LEN(RTRIM(POST_CODE)) - 3)
    FROM table-name
    and to get the counts for the distinct values that are left you can add:

    Code:
    SELECT POST_CODE_FIRST_PART, COUNT(*) AS CNT
    FROM(SELECT RTRIM(SUBSTRING(POST_CODE,1,LEN(RTRIM(POST_CODE)) - 3) AS POST_CODE_FIRST_PART
         FROM table-name
    )
    GROUP BY POST_CODE_FIRST

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Stealth DBA
    If the pattern is to remove the last 3 characters (plus any space that may be left), you can try this
    Haha, I didn't even notice that logic! Muuuuuch better
    George
    Home | Blog

  5. #5
    Join Date
    Sep 2009
    Posts
    19
    Thank you both for you suggestions! They both work but obviously the second is simpler!

    Thanks a lot!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •