Skip to main content
P
peggyscott
Participating Frequently
September 16, 2009
Question

newbie question: How do I add line breaks to an nvarchar variable?

  • September 16, 2009
  • 2 replies
  • 3256 views

I am building a ColdFusion website for the first time. I am using ColdFusion 8

I have a MS SQL Server 2008 database that I need to query.

I need to concatenate nvarchar variables into one nvarchar  variable separated by new lines.

(I am using commas for now but it looks messy)

I using the XML blackbox method from

http://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/

I need to display the results on my ColdFusion website.

I  tried using Char(9) Char(10) Char(13)

Chr(13) etc. None of these work for the website. They just add new lines when I print the results on management studio.

I have seen this question posted in several forums but no answers.

Someone suggested using a reporting tool in stead but no references.

Please help!

This topic has been closed for replies.

2 replies

ilssac
ilssac
Inspiring
September 16, 2009

It is not working because the HTML standard says that most whitespace characters, including tabs, line feeds and carriage returns are ignored when the page is displayed.

Unless one used the <pre></pre> tag, which is designed to tell HTML to honor all whitespace characters.  The default for most browsers is to display content in pre tags in a monospaced font.  But that is controled with stylesheet code.

OR

You could use the proper HTML tags that designate spacing which would be the <p>...</p> and|or the <br/> tags.  Note that is <br/>  NOT <br></br> that you listed.  The break tag is not an open/close set.

P
peggyscottAuthor
Participating Frequently
September 17, 2009

thanks for the response Ian but I I tried <br> and </br> in separate cases as you suggested (thats what I meant by my earlier post)

not together and it still does not work

P
peggyscottAuthor
Participating Frequently
September 17, 2009

for those who need the actual code:

CREATE FUNCTION DetailProblems

(

@SelectedID int

)

RETURNS

@t TABLE

(

ID INT NOT NULL,

Problems NVARCHAR(4000)

)

AS

BEGIN

    Declare @ProblemTable TABLE(

ID INT NOT NULL,

Problem NVARCHAR(50));

Insert Into @ProblemTable

SELECT ProblemJunction.ID, ProblemLookUp.PrimaryProblem

FROM ICRRR.dbo.ProblemJunction INNER JOIN ICRRR.dbo.ProblemLookUp

ON ProblemJunction.ProblemNumber = ProblemLookUp.ProblemNumber

WHERE ID = @SelectedID

INSERT @t (ID, Problems)

SELECT DISTINCT ID,

STUFF((SELECT '</br>' + Problem

FROM @ProblemTable AS B

WHERE B.ID = A.ID

FOR XML PATH('')),1,1,'')

FROM @ProblemTable AS A

IF NOT EXISTS (SELECT * FROM @t)

INSERT @t VALUES (@SelectedID, N'Unknown')

RETURN

END

is the function I am using.

the output is as follows:

lt;/br>Invasive plant species</br>Degraded Riparian Habitat

P
peggyscottAuthor
Participating Frequently
September 16, 2009

I have tried adding <br> and </br>

doesn't work

I am trying to avoid using replace.

I think there must be a better way

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
September 16, 2009

Can you post your code and a description of what you're trying to accomplish?

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices