Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    2

    Unanswered: Extract UK Postcode Sectors

    Hi there, I'm fairly new to SQL so hopefully someone more experienced can help with this?

    I have an SQL database and one of the tables contains UK addresses.

    One of the fields in my table contains a Postcode field.

    UK postcodes are split into two portions with a space in the middle. The first portion is variable length between 2 and 4 characters, and the second portion is generally 3 characters (at least in all the postcodes I've seen anyway).

    eg:

    E3 4RT
    AB1 2AB
    EC12 4TR

    I'd like to select the unique postal sectors from my table, which is the entire first portion before the space and the first character after the space.

    Using the examples above that would be:

    E3 4
    AB1 2
    EC12 4

    If the first portion was a uniform length I'd simply use LEFT, but it's not

    Is there some syntax of SELECT that I can use which does this?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Ten98 View Post
    Is there some syntax of SELECT that I can use which does this?
    yeah, there is, but it would perhaps be quicker in the long run if you mentioned which database system you're using, as the string handling functions vary from one to the other, and the ANSI SQL syntax you would normally get in this forum might not work for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jun 2011
    Posts
    2
    Oh ok, didn't realise that was a thing.

    I'm using MS SQL Server 2008 if that helps?

  4. #4
    Join Date
    Mar 2009
    Location
    Australia
    Posts
    113
    Code:
    select	distinct 
    		substring(postcode,1,charindex(' ',postcode,1)+1)
    from	postcodetable

  5. #5
    Join Date
    Dec 2008
    Location
    At work...
    Posts
    92
    And EngadaSQL's statement translated to ANSI SQL is pretty similar:

    select distinct
    substring(postcode from 1 for position(' ' in postcode)+1)
    from postcodetable


    (Core SQL-99)

Posting Permissions

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