MySQL brain teaser

7,943 pts.
Tags:
MySQL
MySQL query
SQL
This has been driving me crazy. I’m trying to come up with an UPDATE query in MySQL that will remove the second blank space in a name and close the gap.

 

Example: O Brien, John E should become OBrien, John E

 

A simple REPLACE (name, ‘ ‘,’’) where SUBSTRING (name,2,1) like “ “ won’t work since it would remove all the spaces. I tried nesting the substring into the replace command, with less than stellar results. My last resort would be to parse the name into two new fields and then concat them, but that seems like way too many steps.

 

Has anyone come across an easier way to fix this? Thanks



ASKED: May 18, 2010  3:00 PM
UPDATED: May 19, 2010  6:52 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Well, I can’t think of a way to avoid the parse-concat approach, but you can do it in one operation.

<pre>UPDATE YourTable
SET name = CONCAT(LEFT(name,INSTR(name,’ ‘)-1),RIGHT(name,LENGTH(name)-INSTR(name,’ ‘)))
WHERE …</pre>

I used the INSTR function to find the first blank space position in case it is not always in the second position, but it could be avoided if it does.

———– kccrosser

What is wrong with a simple expression like the following?

set Name = rtrim(left(name, 2)) + substring(name, 3, 255)

If there is a blank in position 2, it will be removed. If not, then nothing changes.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Michael Morisy
    [...] MySQL brain teaser itknowledgeexchange.techtarget.com/itanswers/mysql-brain-teaser – view page – cached This has been driving me crazy. I’m trying to come up with an UPDATE query in MySQL that will remove the second blank space in a name and close the gap.   Example: O Brien, John E should become OBrien, John E   A simple REPLACE (name, ‘ ‘,’’) where SUBSTRING (name,2,1) like “ “ won’t work since it would remove all the spaces. Tweets about this link Topsy.Data.Twitter.User['crusher221'] = {"photo":"","url":"http://twitter.com/crusher221","nick":"crusher221"}; crusher221: “MySQL brain teaser...solutions welcomed! http://bit.ly/aVYpmQ ” 39 minutes ago view tweet retweet Topsy.Data.Twitter.User['itke'] = {"photo":"http://a3.twimg.com/profile_images/870563149/ITKE_normal.jpg","url":"http://twitter.com/itke","nick":"itke"}; itke: “Any solutions to a #MySQL brain teaser http://bit.ly/cbzJnG ” 2 hours ago view tweet retweet Filter tweets [...]
    0 pointsBadges:
    report
  • ITKE
    Thanks Carlos! -Michael
    56,495 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following