Carriage Returns in Data

I have a ntext field of data. I was trying to use the REPLACE function to
change the carriage returns to spaces, but have not any luck.
Can anyone make any suggestions?
Thank you,
JLFlemingYou don't need the text within <> -- it is just an example to show that
things are working as expected.
Here is an example:
create table #foo (col1 varchar(20))
insert into #foo values ('test')
insert into #foo values ('test
more')
select col1 from #foo
select REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>')
from #foo
Keith
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||You will have write a procedure that loops 8000 character chunks of the data
doing the replace.
Thomas
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming|||I missed the ntext bit the first time I read your post.
Keith
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:eMF47mHYFHA.4036@.tk2msftngp13.phx.gbl...
> You don't need the text within <> -- it is just an example to show that
> things are working as expected.
> Here is an example:
> create table #foo (col1 varchar(20))
> insert into #foo values ('test')
> insert into #foo values ('test
> more')
> select col1 from #foo
> select
> REPLACE(REPLACE(col1,char(13),'<replace_a>'),char(10),'<replace_b>') from
> #foo
>
> --
> Keith
>
> "JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
> news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>|||The best way to do this is outside of SQL Server. Text data is a beast in
SQL Server 2000 and earlier to deal with in SQL. The chunking idea given by
Thomas is feasible, but you have to be careful about your search value
crossing the chunk boundry.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"JLFleming" <JLFleming@.discussions.microsoft.com> wrote in message
news:ABB10B20-5FAB-4F3F-9734-0BB29BDD9053@.microsoft.com...
>I have a ntext field of data. I was trying to use the REPLACE function to
> change the carriage returns to spaces, but have not any luck.
> Can anyone make any suggestions?
> Thank you,
> JLFleming

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...[vbco
l=seagreen]
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing
it
> all appears on the same line.
> Thanks,
> Mike
>[/vbcol]|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
Mike
You can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>
|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike
|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
---Line 1
Line 2
Line 3
(1 row(s) affected)
|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>
|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Carriage Return in Data

I am inserting data into a field that is setup as the datatype ntext and
would like to place carriage returns in the text to format the data.
For example:
This is<new line>my data. (Where <new line> is the code for a new line.)
Would display as:
This is
my data.
I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
Thanks,
MikeYou can add them outside if you wish:
insert <tablename> values ('Here is some text' + char(13) + char(10) + 'and
some additional text on a second line')
Rick Sawtell
MCT, MCSD, MCDBA
"Mike" <mbaith@.yahoo.com> wrote in message
news:etOtCp4iEHA.4092@.TK2MSFTNGP10.phx.gbl...
> I am inserting data into a field that is setup as the datatype ntext and
> would like to place carriage returns in the text to format the data.
> For example:
> This is<new line>my data. (Where <new line> is the code for a new line.)
> Would display as:
> This is
> my data.
> I tried using VBCrLf and Chr(13) & Chr(10), but neither worked.
> Thanks,
> Mike
>|||Rick,
I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
all appears on the same line.
Thanks,
Mike|||When outputting it where? I ran this in query analyzer.
========================================
CREATE TABLE Frog (col1 ntext)
GO
INSERT Frog VALUES ('Line 1' + CHAR(13) + CHAR(10) + 'Line 2' + CHAR(13) +
CHAR(10) + 'Line 3')
GO
SELECT col1 FROM Frog
col1
----
---Line 1
Line 2
Line 3
(1 row(s) affected)|||Can you post a repro? Below work just fine in my query analyzer:
SELECT 'Hello ' + CHAR(13) + CHAR(10) + 'there!'
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Mike" <mbaith@.yahoo.com> wrote in message news:Oy39A14iEHA.596@.TK2MSFTNGP11.phx.gbl...
> Rick,
> I have tried using Chr(13) & Chr(10), but it doesn't work. When outputing it
> all appears on the same line.
> Thanks,
> Mike
>|||Rick,
I was outputting it to a web page, which apparently ignores char 13 & 10...
I used a replace to replace the char 13 & 10 with a <br> and its working. (I
couldn't put the <br> in the data because the webpage isn't the only
application accessing the data.)
Thanks for your help!
Mike

Cant use the NTEXT datatype in SQLCLR scalar-valued functions

From the SQL Server documentation :

"The input parameters and the type returned from a SVF can be any of the scalar data types supported by SQL Server, exceptrowversion,text,ntext,image,timestamp,table, orcursor"

This is a problem for me. Here's what I'm trying to do :

I have an NTEXT field in one of my tables. I want to run regular expressions on this field, and return the results from a stored procedure. Since SQL Server doesn't provide facilities to perform regular expressions, I need to use an SQLCLR function. I would have no problem doing this if my field was nvarchar. However, this field needs to be variable in length - I cannot set an upper bound. This is why I'm using NTEXT and not nvarchar in the first place.

Is there a solution to this problem? I can't imagine that I'm the only person who wants to pass strings of arbitrary size to an SQLCLR function.

The sql server 2005 nvarchar(max) is the same as sql server 2000 nText

http://msdn2.microsoft.com/en-us/library/ms178158.aspx

|||Cool. Thanks for the tip. I'll give this a shot and report back.