Solved

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

Posted on 2010-08-24
26
500 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

738 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