Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2011
    Posts
    83

    Unanswered: Spaces within post codes

    Hi,

    I am trying to merge two tables of customers and I am working on the POST CODR field. Some have spaces between the letters and other s dont. How do I delete all the spaces in the psot code field?

    Thanks

    Simon

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could use the Replace() function in a query:
    Code:
    INSERT INTO DestinationTable ( PostCode, some other fields...)
    SELECT Replace([PostCode], ' ', ''), some other fields...
    FROM SourceTable;
    Have a nice day!

  3. #3
    Join Date
    Jan 2011
    Posts
    83
    Thanks. So if my table is tblCustomer and the field is PostCode, what is the full syntax to put into the SQL query?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I can't provide the full SQL statement as I don't know the names of the columns in both tables (I guess that 'PostCode' isn't the only column), as well as the names of both tables.
    Have a nice day!

  5. #5
    Join Date
    Jan 2011
    Posts
    83
    At the moment, I just want to work on the one table which is called tblCustomers. It has 2 fields. "Customer" and "PostCode"

    I just want to delete all the spaces from that one table.

    Is this possible?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes it is:
    Code:
    UPDATE TblCustomers SET PostCode = Replace([PostCode], ' ', '');
    Have a nice day!

  7. #7
    Join Date
    Jan 2011
    Posts
    83
    Thanks very much

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    deleting space ijn postcodes can cause problems using the data down the line. some systems recognise the spaces and some don't. as its trivial to extract the space in a query I wouldn't store the resultant value. IE store M235WE not M23 5WE.
    you can regenerate the space if you want to but its tricker than removing.

    why would this be a problem?
    well some GIS systems look for the space or only work in the first block
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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