Thank you! This is exactly what I needed!

Unfortunately this doesn't work for me. At least in this function:

create function dbo.get_spid_sql
( @p_sql_handle binary(20) )
returns varchar(8000)
as
begin
declare @curr_stmt varchar(8000)
declare @nl char(2)

select @curr_stmt = [text] from ::fn_get_sql(@p_sql_handle)

set @nl = char(13) + char(10)

set @curr_stmt = replace(@curr_stmt, @nl, ' ')

return @curr_stmt
end

As you can see I want to take a sql handle and return its statement without returns, but that doesn't work.

Oscar,

Are you sure that your new line separators are (char(13)+char(10))?
In some encodings ony single char(13) or char(10) could be used. I even have seen another order (char(10)+char(13)).

Actually in a description column someone is pressing enter button ,so in the description column i have to remove enter and I have to concatenate total sentence.

use this when replacing. It gets everything

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

Michael....thank you very much for your help.

Thank you. I am searching this replace query for a long time. Thank u sir,

Hello guys, i try to uset it, but in iexplores is ok, but in Firefox not. I don't know what i have to do to in firefox also function.

Thank you very much!!!!!
i was searching for this from long time...

Thanks a lot, Xavid!!!!

Thank u very much!

Thanks a lok

It helped me. I imported data from Excel Spread sheet to SQL Table. one junk character is deplaying at the end of some columns. I figured that was one of new line char. I always tried to replace with Char(13) but I did never worked for me. When I replced with Char(10), It worked fine. Thanks a lot for your good article.

Thanks Xavid!

Thanks! It worked for me.

Thanks Xavid!
I had been searching how to do it for quite a while and this really helped !

this works for my query:

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

thanks a lot!!!!! WEEEEEEEEE!!!

I had been trying to clean up some legacy HTML page content data after an import from Access to SQL Server 2005 using the Microsoft SSMA tool. There were all of those little boxes displayed in the grid view when I viewed the data. I was also trying to combining several small Access databases into a single MSQL database and so it was useful to copy and paste some rows from one imported table to my new production tables. I was only able to copy and paste rows if the little boxes for carriage returns and line feeds were removed. In order to that I used the following code to execute directly on the database in SQL Server. The first statement replaces the new lines with HTML breaks so that the website still displays as intended. The second statement just removes the boxes so the rows can be copied and pasted from one MSQL table to another.

UPDATE tblContent
SET Author = REPLACE(Author,char(13),'<br>')
WHERE Author LIKE '%'+char(13)+'%'

UPDATE tblContent
SET Copy = REPLACE(Copy,char(10),'')
WHERE Copy LIKE '%'+char(10)+'%'

Thanks a lot. This really helped me

Thank you Xavid.

It worked!

Way to go guys, way to go!
...@_@...

Hey its really very good ...it helped me a lot...Thanks once again for sharing knowledge....

Thanks ... It was of great use. .. :)

I wanted to pass on my find. BTW, this helped a lot. Big props go out to Xavid!!!

Dylan had a good idea too...

here is THE MOST simple way to correct this issue for a single instance:


!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Update CABLEPR
set [field_name] = REPLACE(REPLACE(REPLACE([field_name], CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

very very useful stuff! Thanks guys!

Thank you.

Interesting,

oh I had to write load and load of codes just to do this simple tast

Keep up the good work

thank you for this blog, it is realy helpful. Thanks much!

Thanks...........
This works for me....
I was searching for that.........
once again Thanks for this post.......

Thanks, It worked for me

Thanks, it really solved my Problem. Thanks again

Very helpful, thanks!

Very helpful, thanks!

Exactly what i was looking for. It work like a charm. Perfect. Thanks a ton.

Thanks for your help, you saved my life.

A lot of thnx, really i need this toooooooooooooo much thnx thnx and thnx

I am trying to insert new line character like
set @NewLine=char(13)+char(10) in sql server but it will not work in sql server 2008..i don't known why ..u have any new line option for sql server 2008.....................

Thanks Xavid!!!

Thanks a lot It saved my time

Thansks very good !!!

I used the following query...
Select 'This is'+ char(13)| + 'for test' as Test
But Its work in query result as text.
But Its not work in query result as grid. But i need to display the result in grid what i can do for this..

Thanks Xavid!!!

Great Thanks....

Thanks i used it for adding new not removing it

Excellent!!!
Resolved a big issue for me.

Thank you very much for this info. It is very helpful.

Thank You very much Solved my problem

it worked fine.... thanks a lot...

thnx dear

Hi Guys,

Thanks for this post, It actually saved my days
Please keep posting !

Thanks
Kamal Patel.

For oracle 10 g its CHR instead of CHAR

THANX XDAVID

Thank you Xavid!!!

I had problems with exporting the resultset to excel and this solved the problem.

New Line characters are hiding when we do Export to Excel

thanks..
worked for me too

very thanx. This is exactly what I needed!

For Oracle databases, one can make use of REPLACE function as follows -
REPLACE(Con.COMMENTS, CHR(13) || CHR(10), ‘ ‘) as “Updated_Comments”

For a detailed example, please refer to following link:
http://crackingsiebel.wordpress.com/2010/08/21/oracle-sql-replace-newline-or-linefeed-character/

Thank You very much Solved my problem :)

Thanks a lot, Finally its worked out

Thank you very much, Xavid.

Thank you Xavid for such a wonderful blog!!! You are marvellous :) !!

Thankyou somuch.. Xavid, its really worked for me.

REPLACE(REPLACE(REPLACE(MyField, CHAR(10), ''), CHAR(13), ''), CHAR(9), '')

This query replaces all instance of CR LF the where ( RIGHT(data_text ,2)=@NewLine) does not work, it still removes all instances of CR LF in the text string. I have multiple sentences in my text and only need to remove the CR LF from the end of the record.

Hi, I´ve been working with REPLACE for changing a '.' for a ',' and it worked perfect. Although, when I specify char(13), equivalen to CR there is an error: Ora 0936: MIssing expression and points the Char(13) in the sentence. Could be a problem with the SQL version, in that case, what sentence should I use instead of Char()?
I appreciate a lot your help.

Regards,
Jorge

Thanks a lot ...It is very much helpful

Wonderful it's worked for me!!