removing whitespace from stuff already in the db

steamPunk

I've just taken on a db with a table ("pl") containing a list of shops - each shop has a 5-digit post code like "12345" - I use this post code to make a list of all postcodes in that table like this

$query = "SELECT * FROM pl GROUP BY PL_CP ASC";

the problem is that some of the post codes have been entered with whitespace like this "12345 "

this creates the problem that GROUP BY obviously sees "12345" and "12345 " as two different things

So i either need to be able to go through all post codes in the db and trim off the whitespace - or write a query that ignores whitespace if such a thing is possible

what do you think would be the best solution here ??

thanks

laserlight

I suggest using [man]preg_replace/man to ensure that only significant characters are stored.

If the data is already present, then you either select, use preg_replace(), then update, or you use a replacement function specific to the SQL dialect of your DBMS (e.g., REPLACE in MySQL). I do not think that such a function exists in standard SQL.

steamPunk

could there be a way to do it using LIKE and somehow integrating the value PL_CP of the current row ?

ie :

UPDATE pl SET PL_CP=[PL_CP val of current row] WHERE PL_CP LIKE "%[PL_CP val of current row]%"

could such a thing be possible ??

steamPunk

i've just realized that won't work cos the current value also contains the whitespaces

I'll have a look at what you posted

thanks

Roger_Ramjet

Use TRIM in the query to loose white space, eg

SELECT Trim(PL_CP) AS PL_CP FROM pl GROUP BY PL_CP

steamPunk

ah that looks dead interesting too

thanks