[RESOLVED] How do I strip trailing spaces(etc.) from the end of MySQL field data?

Joseph_Sliker

...I mistakenly posted this as a reply elsewhere.

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.

Joseph_Sliker

Okay. I figured it out. Hopefully it will be useful to someone else.

example: "UPDATE tablename SET username=RTRIM(username)"

This zipped through my table in a couple of seconds and put everything right.

This will strip all trailing spaces from the original field value.

ie "joeshmoe " will become "joeshmoe"

BUT it does not fix items that have both trailing spaces and characters.

ie "joeshmoe $%^ " will become "joeshmoe $%^" ...it leaves the first set of spaces in place.

laserlight

Joseph Sliker wrote:
BUT it does not fix items that have both trailing spaces and characters.

ie "joeshmoe $%^ " will become "joeshmoe $%^" ...it leaves the first set of spaces in place.

As in you want the final result to be "joeshmoe$%^"?

Weedpacket

Or "joeshmoe"?

Can usernames contain spaces at all (e.g. "Joseph Sliker" 🙂)? If so, how would you decide which "last words" in the value are legitimate and which are junk?

Joseph_Sliker

Thanks for the replies.

What I want is to strip anything and everything that follows the very first space.

The usernames I allow are strings with NO spaces, so everything after the first appearance of a space is garbage (introduced by some errant bit of bad code from one of my earlier scripts, no doubt).

The situation I needed to deal with yesterday was largely fixed with the simple trim() query in that there were about 700 entries with varying numbers of trailing spaces (and nothing else). There were a handful of others with additional spaces and/or stray characters that I had to deal with "a la carte" which was not too big of a deal.

This is what I get for trying to learn this here computin' stuff from somewheres in the middle.

laserlight

Joseph Sliker wrote:
What I want is to strip anything and everything that follows the very first space.

MySQL, you say? It looks like the SUBSTRING_INDEX function will do the job.

Joseph_Sliker

Thanks! That looks like it will do the trick!

You all are so great.