MySQL

How to remove extra spaces in a row??

Please support our MySQL advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Aug 2008

Posts: 28

Reputation: Vandithar is an unknown quantity at this point

Solved Threads: 0

Vandithar

Offline

Light Poster

How to remove extra spaces in a row??

Jun 2nd, 2009

Hi,

I have a table like this:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 
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)
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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
 
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)
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

Last edited by Vandithar; Jun 2nd, 2009 at 6:41 am.

•

Join Date: Nov 2007

Posts: 11

Reputation: k8f1 is an unknown quantity at this point

Solved Threads: 1

k8f1

Offline

Newbie Poster

Re: How to remove extra spaces in a row??

-1

Jun 2nd, 2009

Hi,

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

Thanks

•

Join Date: Aug 2008

Posts: 28

Reputation: Vandithar is an unknown quantity at this point

Solved Threads: 0

Vandithar

Offline

Light Poster

Re: How to remove extra spaces in a row??

Jun 2nd, 2009

•

Originally Posted by k8f1

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.

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
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)
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:

 Help with Code Tags 
 MySQL Syntax (Toggle Plain Text) 
UPDATE remove SET name = TRIM(name);
update remove set name = trim(name);

but didn't work!!!

How can i remove these spaces??

Regards
Vanditha

•

Join Date: Apr 2009

Posts: 11

Reputation: saurav.prasad28 is an unknown quantity at this point

Solved Threads: 0

saurav.prasad28 saurav.prasad28 is offline

Offline

Newbie Poster

Re: How to remove extra spaces in a row??

Jun 2nd, 2009

•

Originally Posted by Vandithar

Hi,

I am not using css!

This is the data i.e in my table.
Help with Code Tags

MySQL Syntax (Toggle Plain Text)
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)
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:
Help with Code Tags

MySQL Syntax (Toggle Plain Text)
UPDATE remove SET name = TRIM(name);
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

•

Join Date: Nov 2008

Posts: 822

Reputation: verruckt24 is a jewel in the rough

Solved Threads: 73

verruckt24 verruckt24 is offline

Offline

Practically a Posting Shark

Re: How to remove extra spaces in a row??

Jun 3rd, 2009

•

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

Get up every morning and take a look at the Forbes' list of richest people. If your name doesn't appear.... GET TO WORK !!!