Member Avatar for Vandithar

Hi,

I have a table like this:

name
+------------------------------------+
| name                               |
+------------------------------------+
| This is an rna-binding     protein. The data which is useful       is available from the desired             location.  |
+------------------------------------+
1 row in set (0.00 sec)

I want to remove extra space i.e in between binding and protein, desired and location, useful and this.

I tried with trim function but that didn't remove these spaces.

There should be only one space in between binding and protein, desired and location, useful and this.

Output should be like this:

select * from remove;

+--------------------------------+
| name                           |
+--------------------------------+
| This is an rna-binding protein. The data which is useful is available from the desired location |
+--------------------------------+
1 row in set (0.00 sec)

How can i remove extra spaces???

Any suggestions??

Regards
Vanditha

  • 4 Contributors
  • 4 Replies
  • 193 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by verruckt24

Dani AI

Generated

TRIM only removes leading/trailing characters — it does not collapse runs of spaces inside the string — so TRIM() won’t fix the internal multiple spaces in your example. (dev.mysql.com)

If your server is MySQL 8.0.4 or later, the easiest, safest fix is to use the regular-expression replace function to turn any run of whitespace into a single space, then trim ends. Preview first, then apply the UPDATE:

-- preview (no change yet)
SELECT id, name,
       TRIM(REGEXP_REPLACE(name, '\\s+', ' ')) AS cleaned
FROM your_table
WHERE name REGEXP '\\s{2,}';
-- apply when you are happy with the preview
UPDATE your_table
SET name = TRIM(REGEXP_REPLACE(name, '\\s+', ' '))
WHERE name REGEXP '\\s{2,}';

REGEXP_REPLACE is the function that performs the regex-based substitution. (Regex functions were added in the MySQL 8 series.) (dev.mysql.com)

If you are on an older MySQL version without REGEXP_REPLACE, use a REPLACE-based approach. Two practical options: (a) run a small stored routine that repeatedly replaces double spaces with single spaces until none remain, or (b) use nested REPLACE calls to collapse likely runs. Example stored function:

DELIMITER $$
CREATE FUNCTION squeeze_spaces(s TEXT) RETURNS TEXT DETERMINISTIC
BEGIN
  WHILE INSTR(s, '  ') > 0 DO
    SET s = REPLACE(s, '  ', ' ');
  END WHILE;
  RETURN TRIM(s);
END$$
DELIMITER ;

Then: UPDATE your_table SET name = squeeze_spaces(name) WHERE name LIKE '% %'; The basic REPLACE behaviour is described in the MySQL docs. (dev.mysql.com)

Quick practical tips: (1) test with SELECT and keep a backup or update a new column first, (2) watch for tabs or non‑breaking spaces — normalize them with REPLACE(name, CHAR(9), ' ') and REPLACE(name, CHAR(160), ' ') before collapsing spaces, and (3) as noted, regex is ideal when available; ’s REPLACE suggestion is the right direction for older servers.

Recommended Answers

All 4 Replies

Member Avatar for k8f1

Hi,

Could you copy and paste your code? Have you used any css?

Thanks

commented: If you don't know the answer means don't post, don't ask unnecessary questions -1
Member Avatar for Vandithar

Hi,

Could you copy and paste your code? Have you used any css?

Thanks

Hi,

I am not using css!

This is the data i.e in my table.

name
+------------------------------------+
| name                               |
+------------------------------------+
| This is an rna-binding     protein. The data which is useful       is available from the desired             location.  |
+------------------------------------+
1 row in set (0.00 sec)

I tried with trim function like this:

update remove set name = trim(name);

but didn't work!!!

How can i remove these spaces??


Regards
Vanditha

Member Avatar for saurav.prasad28

Hi,

I am not using css!

This is the data i.e in my table.

name
+------------------------------------+
| name                               |
+------------------------------------+
| This is an rna-binding     protein. The data which is useful       is available from the desired             location.  |
+------------------------------------+
1 row in set (0.00 sec)

I tried with trim function like this:

update remove set name = trim(name);

but didn't work!!!

How can i remove these spaces??


Regards
Vanditha

Hi Vanditha

you are right when u used the trim it's not worked because trim is used only when the white space in left side or right side. so use regexp for this issue.

Regards
Saurav Prasad

Member Avatar for verruckt24

Could you copy and paste your code? Have you used any css?

so use regexp for this issue.

Whats code got to do with it ? And what the hell is the css doing there in a question related to MySQL ? And how are you going to use regex Mr. ? If you don't know the answer don't post there's no point in pointing someone in the wrong direction or even asking unnecessary questions.

@OP : Use the REPLACE function in MySQL.
http://dev.mysql.com/doc/refman/5.0/en/replace.html

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.