Posted on 2007-07-26
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
    LVL 19

    Assisted Solution

    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

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

    LVL 39

    Accepted Solution

    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.

    LVL 19

    Assisted Solution

    another thing you can do is something like this

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


    Author Comment


    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

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

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    733 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now