How to add new line in SQL string in SQL server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

hi all
I am using SQL server 2008
I have a string with delimiter full stop(.).

I want to separate string in the next line to display in the Crystal report

string is like

str ='aaaaaaaaaaaaaa.bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd'

result
aaaaaaaaaaaaaaa
bbbbbbbbbbbbbbb
ccccccccccccccc
ddddddddddddddd

Pls help

regards
Nirmala

What I have tried:

DECLARE @strInput nVARCHAR(Max), @strResult nVARCHAR(max);
SET @strInput = 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa,bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb,cccccccccccccccccccccccccccccc.ddddddddddddddddddddddd.eeeeeeeeeeeeeeeeeeeeeeeeeeeee';
SET @strResult = REPLACE(@strInput, ',',CHAR(13));
SELECT @strResult AS 'String with Line Feed';
GO

Posted 28-Dec-21 18:57pm

Nirmala Saravanan

Updated 29-Dec-21 7:26am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2021-12-28T19:21:00

Depending on where you want to use the text the newline may vary. In Windows the newline is using two characters; Carriage return and line feed. In order to replace dots with cr+lf have a try with

SQL

DECLARE @strInput nVARCHAR(Max), @strResult nVARCHAR(max);
SET @strInput = 'aaaaaaaaaaaaaa.bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd';
SET @strResult = REPLACE(@strInput, '.', char(13) + char(10));
SELECT @strResult AS 'String with Line Feed';
GO

Also note that in your code example you have both dots and commas so you may have to do two separate replace calls, depending on the requirements.

Maciej Los · Answer 2 · 2021-12-29T07:26:00

In a newer version of SQL Server you can use STRING_SPLIT (Transact-SQL) - SQL Server | Microsoft Docs[^].

For older version of SQL Server, you need to create custom function (for example: Converting comma separated data in a column to rows for selection[^] ) or write query, which uses CTE[^].
See:

SQL

CREATE TABLE StupidData
(
   LongString NVARCHAR(MAX)
)

INSERT INTO StupidData (LongString)
VALUES(N'aaaaaaaaaaaaaa.bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd');

;WITH CTE AS
(
  SELECT 1 AS Level, LEFT(LongString, CHARINDEX('.', LongString)-1) AS WordPart, 
    RIGHT(LongString, LEN(LongString)- CHARINDEX('.', LongString)) AS Remainder
  FROM StupidData
  WHERE CHARINDEX('.', LongString)>0
  UNION ALL
  SELECT Level + 1 AS Level, LEFT(Remainder, CHARINDEX('.', Remainder)-1) AS WordPart, 
    RIGHT(Remainder, LEN(Remainder)- CHARINDEX('.', Remainder)) AS Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder)>0
  UNION ALL
  SELECT Level + 1 AS Level, Remainder AS WordPart, NULL AS Remainder
  FROM CTE
  WHERE CHARINDEX('.', Remainder)=0
)
SELECT *
FROM CTE

Returns:

Level 	WordPart 	Remainder
1 	aaaaaaaaaaaaaa 	bbbbbbbbbbbbb.cccccccccccccc.ddddddddddddddd
2 	bbbbbbbbbbbbb 	cccccccccccccc.ddddddddddddddd
3 	cccccccccccccc 	ddddddddddddddd
4 	ddddddddddddddd

Online example: SQL Server 2019 | db<>fiddle[^]

How to add new line in SQL string in SQL server 2008

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0