How to find postcodes with two spaces in the middle?

  • Hi all,

    In a nutshell I'm struggling to extract postcodes from a table that have two spaces in the middle (i.e. aa99 9aa rather than aa99 9aa), could anyone please help me with the tsql?

    The postcodes are in the following formats:

    A9 9AA

    A99 9AA

    AA9 9AA

    AA99 9AA

    A9A 9AA

    AA9A 9AA

    I've so far tried using the patindex function:

    select p_code

    from Postcode

    where

    patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0;

    along with the like predicate but to no avail.

    Many thanks in advance.


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • i think you can just search for double spaces, and not whether they follow the rest/standard formatting for like rules right?

    if double spaces are not allowed, you could just replace all of them with a simple update/replace.

    also your current rule should be OR and not AND; one string cannot match All six patterns at the same time, which is what the AND is doing.

    ;WITH Postcode([p_code])

    AS

    (

    SELECT 'A9 9AA' UNION ALL

    SELECT 'A99 9AA' UNION ALL

    SELECT 'AA9 9AA' UNION ALL

    SELECT 'AA99 9AA' UNION ALL

    SELECT 'A9A 9AA' UNION ALL

    SELECT 'AA9A 9AA'

    )

    SELECT * FROM Postcode WHERE p_code LIKE'% %'

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Reading the question I too thought why not you use a like '% %' or a '% % %' if they are not adjacent. Unless we are not understanding the question correctly

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • your original six part WHERE finds everything that is "OK", ie does not match the double spaced indexes.

    this might work as w to find your exceptions and outliers:

    select p_code

    from Postcode

    where

    patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0;

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Lowell (5/26/2016)


    your original six part WHERE finds everything that is "OK", ie does not match the double spaced indexes.

    this might work as w to find your exceptions and outliers:

    select p_code

    from Postcode

    where

    patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0 OR

    patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) > 0;

    Many thanks for your help with this one, apologies if I'm coming across a little dim! I did indeed try to use LIKE '% %' at first but to no avail. Tried your solutions above and again no dice!

    Not sure if this will help but this is the test table and data I've been using to develop the script:

    CREATE TABLE [dbo].[Postcode](

    [p_code] [nchar](10) NULL

    ) ON [PRIMARY]

    GO

    insert into dbo.Postcode

    values

    ('L2 3SW'),

    ('M16 0RA'),

    ('NW3 2RR'),

    ('EH12 9DN'),

    ('W1A 1HQ'),

    ('SW1A 2AA'),

    ('SN2 2HL'),

    ('SN2 2HL'),

    ('SN25 4DL'),

    ('SN25 4DL'),

    ('L2 3SW'),

    ('M16 0RA'),

    ('NW3 2RR'),

    ('EH12 9DN'),

    ('W1A 1HQ'),

    ('SW1A 2AA');

    GO


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • still not sure what are after....thanks for the sample data, would be good if you posted expected results based on that sample..

    maybe...??

    select REPLACE(p_code,' ',' ') pcode

    from Postcode

    where

    patindex('[A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9][0-9] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0 and

    patindex('[A-Z][A-Z][0-9][A-Z] [0-9][A-Z][A-Z]', p_code) = 0;

    ________________________________________________________________
    you can lead a user to data....but you cannot make them think
    and remember....every day is a school day

  • Probably this could help. The problem is that you have a char(10) and you're looking for 8-character strings.

    SELECT *

    FROM dbo.Postcode

    WHERE p_code LIKE N'___ ___ ';

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (5/26/2016)


    Probably this could help. The problem is that you have a char(10) and you're looking for 8-character strings.

    SELECT *

    FROM dbo.Postcode

    WHERE p_code LIKE N'___ ___ ';

    Good call! This now works with some adjustments for the various other character lengths, many thanks.

    I also figured we can extract the entire data set (15000 records) from the address table in the original database (not the test one I'm working on), export it to Excel and do a simple find and replace (i.e. find what = 2 spaces, replace with = 1 space). This then removes the errant spaces from the postcodes and we can import the data back into the database!!!

    Thanks for all the help on this one though as I've learnt a lot.

    Many thanks.

    Mark.


    MCITP
    MCTS - E-Business Card
    Twitter: WWDMark

    Try not! Do or do not, there is no try

    email: info@weekendwebdesign.co.uk
    Personal Website: http://markallen.co.uk/
    Business Website: https://www.weekendwebdesign.co.uk

  • export it to Excel and do a simple find and replace

    What is wrong with

    UPDATE yourtable

    SET Postcode = Replace(Postcode, ' ', ' ')

    FROM yourtable

    WHERE postcode LIKE N'___ ___ ';

    No need to export or re-import...

    Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.

    When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara

  • EdVassie (5/27/2016)


    export it to Excel and do a simple find and replace

    What is wrong with

    UPDATE yourtable

    SET Postcode = Replace(Postcode, ' ', ' ')

    FROM yourtable

    WHERE postcode LIKE N'___ ___ ';

    No need to export or re-import...

    Or even, to deal with trailing spaces;

    UPDATE dbo.Postcode

    SET p_code = RTRIM(REPLACE(p_code,' ', ' '))

    FROM dbo.PostCode

    ...

  • HappyGeek (6/20/2016)


    EdVassie (5/27/2016)


    export it to Excel and do a simple find and replace

    What is wrong with

    UPDATE yourtable

    SET Postcode = Replace(Postcode, ' ', ' ')

    FROM yourtable

    WHERE postcode LIKE N'___ ___ ';

    No need to export or re-import...

    Or even, to deal with trailing spaces;

    UPDATE dbo.Postcode

    SET p_code = RTRIM(REPLACE(p_code,' ', ' '))

    FROM dbo.PostCode

    That won't do any good. The column is an nchar, so the trailing spaces will be added.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Luis Cazares (6/20/2016)


    HappyGeek (6/20/2016)


    EdVassie (5/27/2016)


    export it to Excel and do a simple find and replace

    What is wrong with

    UPDATE yourtable

    SET Postcode = Replace(Postcode, ' ', ' ')

    FROM yourtable

    WHERE postcode LIKE N'___ ___ ';

    No need to export or re-import...

    Or even, to deal with trailing spaces;

    UPDATE dbo.Postcode

    SET p_code = RTRIM(REPLACE(p_code,' ', ' '))

    FROM dbo.PostCode

    That won't do any good. The column is an nchar, so the trailing spaces will be added.

    Correct, never spotted that, lesson learned, read the definition!!!

    ...

  • Would the code below work? Would capture anything with 2 or more spaces?

    SELECT *

    FROM dbo.Postcode AS p

    WHERE LEN(RTRIM(p_code)) - LEN(REPLACE(RTRIM(p_code),' ', '')) >=2

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply