Solved

How to add line feed and carriage return to bullets in a string

Posted on 2010-08-24
26
453 Views
Last Modified: 2012-05-10
I have a string that contains bulleted points all in one line and I want to add carriage return and line feed after every bullet except the first one. Here is the code that is not working for me:

DECLARE @KeyPoints VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SELECT REPLACE(@KeyPoints, ' • ', ' • ' + CHAR(13) + CHAR(10)) KeySellPt

When I display this in html, I see all the string in one line rather than multiple lines as should it be after the replace statement.

What is wrong going on here? Any clues?

Thanks
0
Comment
Question by:skaleem1
  • 11
  • 9
  • 6
26 Comments
 
LVL 16

Expert Comment

by:vdr1620
ID: 33514595
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33514680
DECLARE @KeyPoints VARCHAR(1000)
DECLARE @LineFeed VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SET @LineFeed =   CHAR(13) + CHAR(10)
SET @KeyPoints = REPLACE (REPLACE (@KeyPoints , '•' ,@LineFeed ),'•' ,'')
SELECT @KeyPoints
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33514848
As mentioned in my posting, I do not want to remove the bullets as your code does. I want to keep the bullets and want to add a line feed and carriage return just before every bullet so that the each bulleted point is pushed down to the next line.

I changed your code a little bit as follows but still it does not work for me:

DECLARE @KeyPoints VARCHAR(1000)
DECLARE @LineFeed VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'

SET @LineFeed =   CHAR(13) + CHAR(10)
SET @KeyPoints = REPLACE (@KeyPoints , ' • ' , ' • ' + @LineFeed )
SELECT @KeyPoints

Any further clues?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33514920
>>When I display this in html
what if you do like this?
(please note that this will not display "<br>" in SQL....

DECLARE @KeyPoints VARCHAR(1000)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'



SET @KeyPoints = REPLACE (@KeyPoints , ' • ' , ' • <br>' )

SELECT @KeyPoints

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33514928
the last comment should read:

(please note that this will display "<br>" in SQL....
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33514933
Try this
DECLARE @KeyPoints VARCHAR(1000)
DECLARE @LineFeed VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'

SET @LineFeed =   CHAR(13) + CHAR(10)
SET @KeyPoints = REPLACE (@KeyPoints , ' • ' , @LineFeed + ' • '   )
SELECT @KeyPoints
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33514965
and of course if you want it before the bullet
 

DECLARE @KeyPoints VARCHAR(1000)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'



SET @KeyPoints = REPLACE (@KeyPoints , ' • ' , ' <br> • ' )

SELECT @KeyPoints

Open in new window

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33515313
does not work. In html view, it literally displays the <br> tag as follows:

• ABC<br>•DEF<br>•GHI<br>•JKL

Any ideas?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33515446
>>does not work. In html view, it literally displays the <br> tag as follows:

where are you viewing this? please remember that you need the whole <html><body>... </body></html> tags in the text...
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33515494
What about the SQL that i modified.. Does that work for you?

with REPLACE (@KeyPoints , ' • ' , @LineFeed + ' • '   )
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33515612
yes it does not work either...
0
 
LVL 1

Author Comment

by:skaleem1
ID: 33515646
>>where are you viewing this? please remember that you need the whole <html><body>... </body></html> tags in the text...


I am viewing it in a report that is generated by SSRS (SQL Server Reporting Services). This actually is one of the columns returned from a stored procedure dataset.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33515658
Please answer my comment http:#a33515446
 
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 41

Expert Comment

by:ralmada
ID: 33515667
oops, sorry didn't see your last post.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33515826
can you try this:
DECLARE @KeyPoints VARCHAR(max)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'



SET @KeyPoints = REPLACE (cast(@KeyPoints as nvarchar(max)) , ' • ' , char(13) + char(10) + ' • ' )

SELECT @KeyPoints

Open in new window

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33515932
also probably you need to do this as well
DECLARE @KeyPoints VARCHAR(max)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'



SET @KeyPoints = REPLACE (cast(@KeyPoints as nvarchar(max)) , '•' , char(13) + char(10) + '•' )

SELECT @KeyPoints

Open in new window

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33522064
This one works and even the one posted in the original comment works if I remove the leading and trailing spaces from the searched bullet:

DECLARE @KeyPoints VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SELECT REPLACE(@KeyPoints, '•',  CHAR(13) + CHAR(10) + '•') KeySellPt

However, it also adds a line feed before the first bullet and pushes the top bullet line down, something I do not want to have. I can see that the first bullet in the text does not have a leading space, so if I try the following, it should theoretically work and should not replace the first bullet, but it practically does:

DECLARE @KeyPoints VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SELECT REPLACE(@KeyPoints, ' •',  CHAR(13) + CHAR(10) + ' •') KeySellPt

How to get around this problem?



0
 
LVL 1

Author Comment

by:skaleem1
ID: 33522105
I even tried LTRIM function for the text but with little luck:

DECLARE @KeyPoints VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SELECT REPLACE(LTRIM(@KeyPoints), ' •',  CHAR(13) + CHAR(10) + ' •') KeySellPt

0
 
LVL 41

Expert Comment

by:ralmada
ID: 33522182
try
DECLARE @KeyPoints VARCHAR(1000)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'

SELECT stuff(REPLACE(@KeyPoints, '•',  CHAR(13) + CHAR(10) + '•'), 1, 2, '') KeySellPt

Open in new window

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33523444
Still not working, still pushes the first the top bulleted line down one line
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33523504
can you paste the results of the query, along with expected result set
0
 
LVL 41

Accepted Solution

by:
ralmada earned 300 total points
ID: 33523648
>>Still not working, still pushes the first the top bulleted line down one line <<
are you sure you're trying the correct version? please note that this has the bullet character without the spaces in there.

DECLARE @KeyPoints VARCHAR(1000)

SET @KeyPoints ='• ABC • DEF • GHI • JKL'

SELECT stuff(REPLACE(@KeyPoints, '•',  CHAR(13) + CHAR(10) + '•'), 1, 2, '') KeySellPt

Open in new window

0
 
LVL 1

Author Comment

by:skaleem1
ID: 33524561
Yes the first bullet in the text does not have any leading space character. However, when I try the following (please note that I am replacing all the bullet entries that have leading space, meaning the first should not be replaced having no leading space), it still replaces the first bullet by bullet plus line feed and carriage return characters in addition to all others:

DECLARE @KeyPoints VARCHAR(1000)
SET @KeyPoints ='• ABC • DEF • GHI • JKL'
SELECT REPLACE(LTRIM(@KeyPoints), ' •',  CHAR(13) + CHAR(10) + ' •') KeySellPt

Please note that I am now replacing all bullets with a leading space (' •'). How come it replaces the first bullet that does not have a leading space?
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33525232
well, that's the whole point in my code. it will replace all bullets, but I have added the STUFF function so that the first bullet will NOT have the line feed / carriage return characters before. Please give it a try. again comment http:#a33523648
0
 
LVL 1

Author Closing Comment

by:skaleem1
ID: 33525573
ralmada, I tried your code and finally it worked. Thanks a lot for all your help and time
0
 
LVL 16

Expert Comment

by:vdr1620
ID: 33525574
@Ralmada
 That's the reason i have asked skaleem1 to paste the Copy of result sets.. in my previous post ( ID: 33523504)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to select a spread of rows in SQL 8 54
MS SQL Inner Join - Multiple Join Parameters 2 17
TSQL - IF ELSE? 3 25
Add '#' to end of file 2 29
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

943 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

10 Experts available now in Live!

Get 1:1 Help Now