New lines in textarea

I need to be able to strip out newline characters from data stored in a
textarea, but I've been unable to locate data so far that tells me how
newlines/carriage returns are stored in SQL Server. Any help on this
is greatly appreciated.CHAR(13)+CHAR(10)
http://www.aspfaq.com/2188
<Joiey.Seeley@.gmail.com> wrote in message
news:1131462555.249672.14910@.g47g2000cwa.googlegroups.com...
>I need to be able to strip out newline characters from data stored in a
> textarea, but I've been unable to locate data so far that tells me how
> newlines/carriage returns are stored in SQL Server. Any help on this
> is greatly appreciated.
>|||You can run the following statements to remove CRLFs from a column within a
table
update testtable set col1 = REPLACE ( col1 , char(10) , '' )
update testtable set col1 = REPLACE ( col1 , char(13) , '' )
testtable is your table and col1 is the column you want to do the replace on
.
Hope this helps
--
Adam J Warne, MCDBA
"Joiey.Seeley@.gmail.com" wrote:

> I need to be able to strip out newline characters from data stored in a
> textarea, but I've been unable to locate data so far that tells me how
> newlines/carriage returns are stored in SQL Server. Any help on this
> is greatly appreciated.
>|||I tried to use this method on a TEXT field using the following:
update support_incident set problem = REPLACE ( problem , char(10) , '' )
update support_incident set col1 = REPLACE ( problem , char(13) , '' )
Got this error message:
Server: Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of replace function.
Server: Msg 8116, Level 16, State 1, Line 2
Argument data type text is invalid for argument 1 of replace function.
--
Ray
How do you remove the CRLFs from a text field?
"Adam Warne" wrote:
> You can run the following statements to remove CRLFs from a column within
a
> table
> update testtable set col1 = REPLACE ( col1 , char(10) , '' )
> update testtable set col1 = REPLACE ( col1 , char(13) , '' )
> testtable is your table and col1 is the column you want to do the replace
on.
> Hope this helps
> --
> Adam J Warne, MCDBA
>
> "Joiey.Seeley@.gmail.com" wrote:
>|||http://www.aspfaq.com/2445
"Ray" <ray.smith@.loislaw.com> wrote in message
news:E1FD569C-3F54-42FB-8B45-8C28B04883A5@.microsoft.com...
>I tried to use this method on a TEXT field using the following:
> update support_incident set problem = REPLACE ( problem , char(10) , '' )
> update support_incident set col1 = REPLACE ( problem , char(13) , '' )
> Got this error message:
> Server: Msg 8116, Level 16, State 1, Line 1
> Argument data type text is invalid for argument 1 of replace function.
> Server: Msg 8116, Level 16, State 1, Line 2
> Argument data type text is invalid for argument 1 of replace function.
> --
> Ray
> How do you remove the CRLFs from a text field?
> "Adam Warne" wrote:
>

New line in query

Hi,
I was wondering if there is any way I can place a new line inside a query...
e.g.
select field1 + 'NEWLINE' + field2 from tablename
I want to place a new line between field1 and field2
Thanks in advanceselect field1 + char(13) + field2 from tablename|||Thanks for reply blindman

I have tried this already but it doesn't work :(
any other suggestions would be appreciated.

Thanks|||It works in query analyzer. What are you looking at the results in?

select 'a' + char(13) + 'b'|||I am using MSSQL 2005 Server Management Studio which replaces both the SQL Server 2000 Enterprise Manager and the Query Analyzer.

Thanks|||Odd. It still works for me.

select 'a' + char(13) + 'b'

--
a
b

(1 row(s) affected)|||Actually It works on the text mode result but not Grid mode ...still it doesn't save a and b on different lines in the database itself not sure why

Once I fetch the record it displays it on a single line...

On my other fields where I am saving text from my .net application textboxes it creates double boxes for a newline... I was wondering how would I create thoes double boxes :)|||Ahh, the grid is saving the carriage return, but it displays as a blank in grid mode.

-- Using Northwind

DECLARE @.MyText CHAR(26)
DECLARE @.MyText2 CHAR(26)
SET @.MyText = 'a' + char(13) + 'b'
PRINT @.MyText

SELECT ShipCity
FROM Orders
WHERE OrderID = 10248

UPDATE Orders
SET ShipCity = @.MyText
WHERE OrderID = 10248

SET @.MyText2 = (SELECT ShipCity
FROM Orders
WHERE OrderID = 10248)

PRINT @.MyText2

SELECT ShipCity
FROM Orders
WHERE OrderID = 10248

UPDATE Orders
SET ShipCity = 'Reims'
WHERE OrderID = 10248

Run this in text mode and you will see that the newline is saved.
Run this in grid mode and you will see that the newline is converted.

I hope this helps,|||Still it will show a one straight line when fetching that data ....what I am thinking now is create a tiny function in .net and add new lines there it did worked for my other data before....

"thank you all for the help guys this forum ROCKS :)"|||Why are you concerned with how it looks in Management Studio? Neither Management Studio or Query Analyzer is meant to be used as a user interface or reporting tool.