CODEWITHSUNDEEP
phpmysqltrim
Harinder
Harinder

Reputation: 1257

Trim extra space from column Mysql

I have a old database in which column categories is stored in bad shape ... with extra space in them, example

Hotels     in     London
Hotels        in     Manchester

is there a way i can alter this space inside the table ... if i can remove extra space from categories (middle space) to get output like this 

Hotels in London

Thx

Upvotes: 0

Views: 1283

Answers (4)

Gal Zilberman
Gal Zilberman

Reputation: 66

Another quick and simple method I found is this:

REPLACE(REPLACE(REPLACE(columnName,' ',' !'),'! ',''),' !',' ')

Upvotes: 0

Mudasir Bhat
Mudasir Bhat

Reputation: 51

I had the similar situation where i had to remove the extra spaces while searching value for a particular field.

I have used replace function of mysql, like this

SELECT * FROM `tableName` WHERE post_id = 'xxx' AND replace(`fieldName`,' ','') Like '%JobOppurtunity%' ;

Here what replace does, it recursively removes all the spaces in the fieldName and concatenates the field value and then searches my concatenated string after LIKE keyword.

So if I had field value 'Job           Oppurtunity', it will convert it into 'JobOppurtunity' and obviously I would have already concatenated my search string by any string function or regular expression. Like it did this way 

$txt_search_qry = trim(preg_replace( '/\s+/', '', $txt_search_qry));

Upvotes: 0

Chris Christensen
Chris Christensen

Reputation: 418

Pull the data down into values that you can do work on then remove the extra white space using:

$foo = trim(preg_replace( '/\s+/', ' ', $foo ));

then write it back to the DB.

Upvotes: 3

Sashi Kant
Sashi Kant

Reputation: 13465

You can use the REPLACE function like 

update table set columnName= REPLACE(columnName,'      ',' ')

Upvotes: 3

Related Questions