hi
is there any function in mysql that trim spaces in the middle of a string?
for examle

SELECT FUNCTION_NAME("my sql")
Return "mysql"

From the mysql manual, string functions -

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

Any way to do this as part of a data cleansing routine?

One customer has supplied text that looks like this:
"__100 UF____________________16V_____________6555________ _____ELEC____________SM____"
Another row:
"___0.1UF______________16V____________0603________ _____X7R______________________"

Obviously, it would be much more desirable to store that as:
"100 UF 16V 6555 ELEC SM"
"0.1UF 16V 0603 X7R"

In Excel, the "trim" function removes all whitespace, converting it into single spaces, I believe.
In Access, it doesn't work that way... in fact, displaying the raw description and the TRIM(description), I actually can't even see a difference...

If it helps, I will be running this SQL as part of an VBA script that performs the data import. I'm pulling the data from Excel spreadsheets that have been pre-formatted.

EDIT: Pretend the underscore characters above are spaces!
In a frustratingly ironic twist of fate, THIS FORUM won't even let me post repeated spaces without truncating them down to one or two!
Funny.

Please, please please tell me something like THIS isn't the solution:

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(DBimport.description,"----", "-"),"---","-"),"--","-"),"--","-"),"--","-"),"--","-"),"--","-")

I might have gone a little overboard - but that's part of what I'm trying to exemplify- why it doesn't seem like the right fix.

And, although even this might work 99% of the time, it's pretty Rube-Goldberg and has a finite limitation (although, 384 if I did my math right, for this particular combo)...

That's NOT elegant - there must be a "right" answer... Help me if you know it!

if you were to run an update query several times to replace, say, 2 or 3 spaces with one space.
after you have run the query around 3..6 times you should be down to a single space.. its not nice but it shoudl work

the only other alternatuve I can think of would be to preprocess the file (using say VBA/PERL/PHP/REXX or something similar) to rip out any offending spaces. the preprocessign could take place as part of the batch job that is loading the data

Sadly MySQL doesn't support regexp when doing a replace - a regexp would make this quite an easy task. You could easily do this in PHP or Unix/Linux if you process the file with either of these.

that's your answer -- collapse the spaces in VBA before importing the data into the database