Strip spaces in strings using SQL. How ?

[fabiuz]

Hi,

is it possible to strip spaces between words in a single table field using SQL ?

I find that it is possible to strip spaces at the beginning and at the end but not in the string it self..

anybody could help ?

Fabio

[[deleted]]

Why would you want to do it in SQL at all?

[fabiuz]

Well,

I have made a little search engine using MySQL. One field has name and surname or band name, yes it is a singers search engine...

Every time that the users don't get back any result I receive an e-mail so I could see what they search and I can add it...

In this way, I see that many of them write "namesurname" all togheter without providing any space, so I would like to make a search query also in this way (only in case that no rows are found in other ways).

The only way to make this kind of check is to have original table fild "all togheter"...

Isn't it ?

Are you going to tell me that I should use different field for name and surname ?

Hope no... also beacuse soma music bands have more than 2 words...

Thanks

[Anon]

Well, postgres has a translate function. I would be suprised if mysql did not have something similar

[Joseph_Sliker]

Arrgh! I must have done something careless with an input form or query. I've got at least one table that has values in the username column that contain trailing spaces (and sometimes some additional junk after that).

Is it possible to write a MySQL update query that will just strip everything off of the end of each field beginning at the first appearance of a blank space in that field?

ie. "update tablename set username=(something in here that strips crap off the end of the existing string in username?)"

Thanks.

Then I'll need to start hunting for the offending page.