trim does not take off the line breaks

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

trim does not take off the line breaks

Post by raghavan20 »

trim function does not take off the line breaks...what is the function or any available way to strip off all the spaces and line breaks from a string stored in the DB using Mysql.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

is there is any preg_replace equivalent function in mysql to remove off unwanted characters?
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Re: trim does not take off the line breaks

Post by JayBird »

raghavan20 wrote:trim function does not take off the line breaks...what is the function or any available way to strip off all the spaces and line breaks from a string stored in the DB using Mysql.
Yes it does!?
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

i hope you have read that i have mentioned in mysql.
I am working purely with mysql procedures.

ex:

Code: Select all

select concat(trim( '                sdfsf        

     '), '##');
+---------------------------------------------------------------+
| concat(trim( '                sdfsf        

     '), '##') |
+---------------------------------------------------------------+
| sdfsf        

##                                           |
+---------------------------------------------------------------+
1 row in set
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

ah, i see...back to the coffee and biscuits ImageImage
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

do you think we can use any mysql regex to remove the line breaks?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

is there any reason why you can't select the field and just trim it with php?
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

try this...

Code: Select all

SELECT concat(trim(REPLACE('               sdfsf        

     , '\\n', '')), '##');
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

i changed it a bit...looks like working...thanks for your idea..

Code: Select all

SELECT concat(trim(REPLACE('               sdfsf        

     ', '\r\n', '')), '##'); 
+----------------------------------------------------------------------------------+
| concat(trim(REPLACE('               sdfsf        

     ', '\r\n', '')), '##') |
+----------------------------------------------------------------------------------+
| sdfsf##                                                                          |
+----------------------------------------------------------------------------------+
1 row in set
User avatar
infolock
DevNet Resident
Posts: 1708
Joined: Wed Sep 25, 2002 7:47 pm

Post by infolock »

Np. Wasn't sure if you would have to escape the \n with \, so i just put it in there just in case (hadn't tested it..)

Glad to see it's working though. have fun.
Post Reply