remove newline character from both ends of result

[write2ashokkumar]

hi....

How to remove the new line character in the left and right side of the sting value in mysql?

Can you any one give me the sample query for this?


Regards,
S.Ashokkumar

[cnplnsk]

Hi folks,
update tab set fld_name=select replace(str,'\n ','') [where clause if there];

[write2ashokkumar]

Hi folks,
update tab set fld_name=select replace(str,'\n ','') [where clause if there];

hi...

If we use the replace function to replace the new line, it will repalce all the new lines in the string...i dont want to remove the new line between characters. i want to remove from left side and right side of the string only.

Regards,
S.Ashokkumar.

[code green]

Sorry I can't answer fully but I would think that you need the LEFT and RIGHT string functions along with REPLACE, within a SELECT statement as a subquery of UPDATE

[ScarletPimpernal]

can u post a sample data and ask b'coz i could not understand your question?

hi....

How to remove the new line character in the left and right side of the sting value in mysql?

Can you any one give me the sample query for this?


Regards,
S.Ashokkumar

[ScarletPimpernal]

If u have all the value in that field with newline character at the begining and
the end of the field.

Then u have use this query,


SELECT SUBSTRING(<Fiel dName>,2,LENGTH (<Fieldname>)-2) FROM Table;

Try this... sure this will help you




Thanks,
Pimp.

[Olorin]

hi....

How to remove the new line character in the left and right side of the sting value in mysql?

Can you any one give me the sample query for this?


Regards,
S.Ashokkumar

In MySQL this is quite easy:

select trim(both '\n' from FIELDNAME) from TABLE;

So if your table is "stuff" and the field is "descriptio n" you would say:

select trim(both '\n' from description) from stuff;

I'm searching for an easy way to trim all whitespace (spaces, tabs, and newlines) from strings in MySQL. No luck so far. I can stack up trim calls, but this doesn't work when the whitespace contains a mix of different kinds of whitespace, unless I iteratively apply the stacked trim statement until the new result == the previous result (el yuko).

Why "trim" doesn't kill all leading/trailing whitespace rather than only one flavor of it at a time is beyond me. This capability has been a standard part of all significant programming language string libraries for at least 15 years. *sigh*

Another approach is to write a Java program that connects to your database, loads the strings in question, applies String's trim method (which does the RIGHT THING(tm) by removing ALL leading/trailing white space), and writes them back to the DB. More work up front, but reusable.

Hope this helped!

[rwgunn]

It may not be elegant, but this relatively short option does work for removing all white space.

eg. update table1 set text1 = replace(replace (replace(text1, ' ',''),'\t',''), '\n','');

' A dog jumped
over the moon
and a cat swam the river.'

becomes:

'Adogjumpedover themoonandacats wamtheriver.'


To trim only the leading/following white space and get rid of any embedded formatting characters without losing the space between words, try:

select replace(replace (trim(both ' ' from text1),'\t','') ,'\n',' ') as new_text from test;


' A dog jumped
over the moon
and a cat swam the river.'

becomes:

A dog jumped over the moon and a cat swam the river.

[adriancs]

after query, do it by replacing "\n" with empty string in your programming language.

example of using C#:

string a;
string b = a.Replace("\n", "");

[vijay57]

thank you so much,

grat........wor ked for me


replace(replace (trim(both ' ' from field),'\t','') ,'\n',' '),