asked on

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

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

vdr1620

Check This

http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/
https://www.experts-exchange.com/questions/26426003/Can-not-save-new-line-to-sql.html?cid=1572&anchorAnswerId=33514514#a33514514

vdr1620

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

skaleem1

ASKER

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?

ralmada

>>When I display this in html
what if you do like this?
(please note that this will not display " " in SQL....

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

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

Open in new window

ralmada

the last comment should read:

(please note that this will display " " in SQL....

vdr1620

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

ralmada

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

skaleem1

ASKER

does not work. In html view, it literally displays the tag as follows:

• ABC •DEF •GHI •JKL

Any ideas?

ralmada

>>does not work. In html view, it literally displays the tag as follows:

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

vdr1620

What about the SQL that i modified.. Does that work for you?

with REPLACE (@KeyPoints , ' • ' , @LineFeed + ' • ' )

skaleem1

ASKER

yes it does not work either...

skaleem1

ASKER

>>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.

ralmada

Please answer my comment http:#a33515446

ralmada

oops, sorry didn't see your last post.

ralmada

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

ralmada

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

skaleem1

ASKER

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?

skaleem1

ASKER

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

ralmada

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

skaleem1

ASKER

Still not working, still pushes the first the top bulleted line down one line

vdr1620

can you paste the results of the query, along with expected result set

ASKER CERTIFIED SOLUTION

ralmada

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

skaleem1

ASKER

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?

ralmada

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

skaleem1

ASKER

ralmada, I tried your code and finally it worked. Thanks a lot for all your help and time

vdr1620

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