Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2004
    Posts
    14

    Unanswered: Insert a space into character field

    Hi

    one of my users has a table which contains a field for postcode. the quality of this data is exceptionally poor, and I'm after running an update query to improve the data. Currently we have records where the Postcode field contains:

    Example 1 AB123CD
    Example 2 AB12 3CD
    Example 3 <no data present>

    Example 2 is correctly formatted, so I want to run a script which will insert a space before the 3rd character from the right except where a space already exists (i.e. as in Example 2).

    Any guidance gratefully received (or if more info required please let me know)

    Thanks

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    update my_table
    set postcode = substr(postcode,1,length(poscode)-3)||' '||substr(postcode,-3)
    where substr(postcode,-3,1) <> ' ';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2004
    Posts
    14
    Thanks Beilstwh. I have tried this but am getting an error message which I assume means the data is dirtier than I anticipated. The error is

    [code]
    ERROR at line 1:
    ORA-01401: inserted value too large for column
    [\code]

    As the field is formatted to 8 characters, I guess some records contain a string of 8 characters (with no space) - this is a data quality error and would need to be looked at before running the update as described. Is there a way that I can select the records where the Postcode field is populated with 8 characters and does not have a space before the 3rd to last character? This would allow me to address this issue before attempting to run the Update

  4. #4
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    sure,
    To fix the update simply use

    update my_table
    set postcode = substr(postcode,1,length(poscode)-3)||' '||substr(postcode,-3)
    where substr(postcode,-3,1) <> ' '
    and length(postcode) < 8;


    To find the problem items

    select postcode
    from mytable
    where substr(postcode,-3,1) <> ' '
    and length(postcode) = 8;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  5. #5
    Join Date
    Aug 2004
    Posts
    14
    Just the job! Thanks

  6. #6
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    In 10g there is also REGEXP_REPLACE.

  7. #7
    Join Date
    Aug 2004
    Posts
    14
    Thanks for the heads up.

Posting Permissions

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