Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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
0
skaleem1
Asked:
skaleem1
  • 11
  • 9
  • 6
1 Solution
 
vdr1620Commented:
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
 
skaleem1Author Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
ralmadaCommented:
>>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
 
ralmadaCommented:
the last comment should read:

(please note that this will display "<br>" in SQL....
0
 
vdr1620Commented:
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
 
ralmadaCommented:
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
 
skaleem1Author Commented:
does not work. In html view, it literally displays the <br> tag as follows:

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

Any ideas?
0
 
ralmadaCommented:
>>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
 
vdr1620Commented:
What about the SQL that i modified.. Does that work for you?

with REPLACE (@KeyPoints , ' • ' , @LineFeed + ' • '   )
0
 
skaleem1Author Commented:
yes it does not work either...
0
 
skaleem1Author Commented:
>>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
 
ralmadaCommented:
Please answer my comment http:#a33515446
 
0
 
ralmadaCommented:
oops, sorry didn't see your last post.
0
 
ralmadaCommented:
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
 
ralmadaCommented:
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
 
skaleem1Author Commented:
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
 
skaleem1Author Commented:
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
 
ralmadaCommented:
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
 
skaleem1Author Commented:
Still not working, still pushes the first the top bulleted line down one line
0
 
vdr1620Commented:
can you paste the results of the query, along with expected result set
0
 
ralmadaCommented:
>>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
 
skaleem1Author Commented:
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
 
ralmadaCommented:
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
 
skaleem1Author Commented:
ralmada, I tried your code and finally it worked. Thanks a lot for all your help and time
0
 
vdr1620Commented:
@Ralmada
 That's the reason i have asked skaleem1 to paste the Copy of result sets.. in my previous post ( ID: 33523504)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

  • 11
  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now