Spaces

bizshop

I'm trying to do a select unique from author column in a MySQL table - but the data as input previously unfortunately sometimes has one space and sometimes has two spaces between the first and last name in the column.

How do I fix this in the db or make a query that will ignore varying amounts of blank spaces. THere are over 90k names so I don't want to have to do it manually ;-)

Column is as:

Smith, Bob
but urlencoded that might show up as
Smith,+Bob or
Smith,++Bob

They look the same in the browser - but I need them to be the same in terms of SELECT DISTINCT

trippyd

update name set name = replace(name, '  ', ' ');

note that the second parameter has 2 spaces, the second has one. That should rid the DB of all double spaces in the name column. If there are triple or quadriple spaces, you can just run that sql multiple times until there are no more multiple spaces. It is a bit of a kludge, but it is quick and it will work.

trippyd

whoops, meant to say:

second parameter has 2 spaces, third has one.

David Lyons wrote:

update name set name = replace(name, '  ', ' ');

fleymingmasc

You might clean up the data already in the table by running a process that finds these records. (Anywhere you see a '+' in the following example, substitute a space.)

SELECT * FROM table WHERE field LIKE '%,++%'

Then iterate a loop on the resultset for each row. Scrub out multiple spaces (regular expressions are your friend!) and perform an update:

UPDATE table SET field = $scrubbed_field WHERE id = $id

This process shouldn't take up a whole lot of time, and will save you lots of headaches. Just be sure to scrub all new data before inserting it into your table.

fleymingmasc

Ignore my solution, David Lyons' is much better! (Though not standard SQL, so non-portable.)

spags

I had a similar problem. The best solution I came up with was to just FIX the database and then in the future, check the data before putting it into a database.

I created the following function to remove spaces (any number of spaces greater than one), remove all whitespaces and trailing and ending spaces.

function CleanupSpaces($s) {
return trim(eregi_replace("[[:space:]]{2,}", " ", $s));
}
so +Bob++++Smith would be Bob Smith

Hope this function helps
Nola

Anon

Hi,

I seem to have the same problem however when I ran your update cmd it says that

ERROR: Function 'replace(text, unknown, unknown)' does not exist
Unable to identify a function that satisfies the given argument types
You may need to add explicit typecasts

Any reason why replace is not found. Is this your personal function.

I am using postgres 7.1

Thanks.

trippyd

As stated in the original question, it is MySQL. I would check your postgres docs for a similar function. I am not that familiar with postgres, but I know the replace function is not ANSI SQL.