Spaces within post codes

Yorkshire Lad · #1 (**permalink**) 01-27-12, 08:38

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

Sinndho · #2 (**permalink**) 01-27-12, 08:44

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;

Yorkshire Lad · #3 (**permalink**) 01-27-12, 08:54

Thanks. So if my table is tblCustomer and the field is PostCode, what is the full syntax to put into the SQL query?

Sinndho · #4 (**permalink**) 01-27-12, 10:11

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.

Yorkshire Lad · #5 (**permalink**) 01-27-12, 10:14

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?

Sinndho · #6 (**permalink**) 01-27-12, 11:57

Yes it is:

Code:

UPDATE TblCustomers SET PostCode = Replace([PostCode], ' ', '');

Yorkshire Lad · #7 (**permalink**) 01-30-12, 04:21

Thanks very much

Sinndho · #8 (**permalink**) 01-30-12, 04:25

You're welcome!

healdem · #9 (**permalink**) 01-30-12, 07:32

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

Internet Services

Web Development

Digital Marketing

Consumer Tech