CODEWITHSUNDEEP
mysql
Ravi Ranjan
Ravi Ranjan

Reputation: 125

How to remove extra space between two strings in MySQL

I want to remove extra spaces between two strings using MySQL:

JULIO                         ZAVALA

Kindly help me out here.

Upvotes: 2

Views: 412

Answers (2)

Eduard Uta
Eduard Uta

Reputation: 2607

In order to remove all spaces between BUT still keep one (I asume you still need a valid name, therefore having a space between first and last name), a construction like below will help:

select CONCAT(SUBSTRING('JULIO                         ZAVALA' FROM 1 for LOCATE(' ', 'JULIO                         ZAVALA')), TRIM(REVERSE(SUBSTRING(REVERSE('JULIO                         ZAVALA') FROM 1 for LOCATE(' ', REVERSE('JULIO                         ZAVALA')))))) as sanitized_name;

In short terms, we look for the first occurence of the space and we first keep (substring) the first name then we reverse the string in order to have the last name on the first position so that we look again for the first occurence of the space in order to get the length of the last name and again keep it (substring). We then reverse back the last name and concat it with the first name that has the extra space needed for name validity.

Upvotes: 1

Poorna Senani Gamage
Poorna Senani Gamage

Reputation: 1268

May be this useful to you. try this.

UPDATE
    tablename
SET
    columnname = replace(columnname, ' ', '');

Upvotes: 2

Related Questions