Solved

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

Posted on 2010-08-24
26
436 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
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…

743 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

12 Experts available now in Live!

Get 1:1 Help Now