Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2011
    Posts
    34

    Unanswered: Purge invalid postal codes

    Hi Guys,

    I live in Canada and want to purge out all invalid postal code formats. Can anyone help?

    Here is an example of a Canadian postal code: M5V 2H2

    The format should be
    (Character)(Number)(Character) <space> (Number)(Character)(Number)

    I am going to remove all spaces so spaces dont really matter for me.

    Thanks!
    - Using Oracle 11g with Toad v12

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Regular expressions seem to be an appropriate way to do that.

    Here's an example:
    Code:
    SQL> WITH test AS
      2    (SELECT 'M5V 2H2' pc FROM DUAL UNION
      3     SELECT 'abc 123' FROM DUAL UNION
      4     SELECT '1a2 c3c' FROM DUAL UNION
      5     SELECT 'a1b 2c4' FROM DUAL UNION
      6     SELECT 'xyz' FROM DUAL UNION
      7     SELECT ' a9v4h4' FROM DUAL UNION
      8     SELECT 'M5V2H2' FROM DUAL
      9    )
     10  SELECT pc
     11  FROM test
     12  WHERE REGEXP_LIKE(pc, '^[[:alpha:]]{1}[[:digit:]]{1}[[:alpha:]]{1} [[:digit:]]{1}[[:alpha:]]{1}[[:digit:]]{1}$');
    
    PC
    -------
    M5V 2H2
    a1b 2c4
    
    SQL>
    It takes care about exact code format you posted (including a space in between, no leading or trailing spaces and similar).

    As you want to remove invalid codes, your query would probably look like
    Code:
    delete from your_table
    where NOT regexp_like ...

  3. #3
    Join Date
    Aug 2011
    Posts
    34
    Littlefoot, thank you very much!

    Do you know how I can format postal codes to o0o 0o0 if the original postal is not in REGEXP_LIKE(pc, '^[[:alpha:]]{1}[[:digit:]]{1}[[:alpha:]]{1} [[:digit:]]{1}[[:alpha:]]{1}[[:digit:]]{1}$') format? I dont not want to delete customers from my database.

    Thanks again
    - Using Oracle 11g with Toad v12

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I live in Canada and want to purge out all invalid postal code formats
    > I dont not want to delete customers from my database.
    Then just update that POSTAL_CODE column to NULL or other "default" value
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by nadecian View Post
    how I can format postal codes to o0o 0o0 if the original postal is not in REGEXP_LIKE ...
    Well, that depends on what current postal codes look like. You should know distinct invalid formats so that you could "convert" them into the correct format.

Posting Permissions

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