Question 208805 in Mysql Question posted in
The official MySQL documentation can be found here.

Extra space between two words in a string in MYSQL table

I have an MYSQL 8 table in which the full name of the same person (LN FN MI) is listed two different ways (two different records). Record#1 is MCCAULEY KELLY JO and Record#2 is MC CAULEY KELLY JO. There should be no space between MC and CAULEY. This occurs for hundreds of names that begin with ‘MC’ or other combos like ‘MR’. What is the best way to delete the records with the space? enter image description here

At first, I tried the REPLACE COMMAND. If I wanted to replace all of the spaces, it would work but I don’t want to replace all of them. If that would have worked I would have deleted the duplicate record.

Tags:

2

Answers


  1. 0

    You could try something like this using the wildcard symbol in SQL (%):

    Delete from table where PartyName like 'MC %' or PartyName like 'MR %'
    Login or Signup to reply.
  2. 0

    Using regular expression might also be helpful:

    delete from table
where PartyName REGEXP 'MC[[:space:]]|MR[[:space:]]';
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search