Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium



Posted on 2007-07-26
Medium Priority
Last Modified: 2013-12-24
Maybe this is easier than I think but I need to ask anyway.  

1)  I use SQL Server NTEXT and the only way I can have paragraph line breaks stored in the fields is by putting the <p> at the end of the sentence, where I want the next sentence to have 2 line spaces between.  Is there a way to format text in NTEXT that handles <p> tag in SQL Server 2005?  Is there a better way to do this?

2)  When I run a query to send specific information from the dbase it populates the CFMAIL output OK, but the <p> tags are visible.  Is there a way to make the <p> invisible or a way to script this so that it ignores the <p> tag?

Question by:bodachs
  • 3
  • 2
LVL 19

Assisted Solution

by:William Elliott
William Elliott earned 1000 total points
ID: 19580030
use INSERT statements to enter your data. Open Table does not permit you to insert line break.

SQL 2005 offers a new data type nvarchar(MAX) which just
like ntext permits unlimited amount of text, but that does not have
all the quirks and limitations of ntext.

Author Comment

ID: 19581067
does NVARCHAR (MAX) handle line breaks and paragraphs without having to clarify them?

LVL 39

Accepted Solution

gdemaria earned 1000 total points
ID: 19582161
1) try using paragraphFormat() when displaying your text to the screen

    such as this  #paragraphFormat(theString)#

    Alternatively, you can do this...

          rereplace(thestring, chr(10)&chr(13), "<br>","all)
or to skip a line this..
          rereplace(thestring, chr(10)&chr(13), "<br><br>","all)

2)  if you have HTML in your CFMAIL, then add the parameter Type="HTML"  like this...
       <CFMAIL Type="HTML"  from=.....

I agree with bodachs about using varchar(max) instead of text.   If you are running SQL Server 2005.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

LVL 19

Assisted Solution

by:William Elliott
William Elliott earned 1000 total points
ID: 19582774
another thing you can do is something like this

SET    @sCRLF = char(13)
SET    @results = @results + @sCRLF
PRINT @results


Author Comment

ID: 19586706

Thanks for the info, I will try both.  All look like good work arounds.  I did change the NVARCHAR to MAX instead of NTEXT, the downside is of course losing 4000 characters.  I am using SQL Server 2005 so we are talking the same platform and appreciate everyone's input.  I just want to make sure it looks right to the user who did the input and the person who receives the e-mail.  

I will let you know what I find.

Author Comment

ID: 19586797
#paragraphFormat(theString)# works GREAT!  It takes all the guess work out of the code!!!!   Thanks so much!!!!

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question