SQL Query | How to insert line breaks in data | Carriage return | Line feed

The query explained in the video is provided below -

declare @data nvarchar(100);
set @data = 'This is line1.This is line2.This is line3.This is line4.';
--set @data1 = 'This is line1.' + CHAR(10) + CHAR(13) + 'This is line2.'

--This is line3.This is line4.';

Check out the complete list of SQL Query Interview Questions -

Please do not forget to like, subscribe and share.

For enrolling and enquiries, please contact us at

[agduce]

Thanks for another good video. Just one minor correction - the order of the CHAR values should be CHAR(13) + CHAR(10), as this is CR and LF characters respectively.

[jacksparrowcapitan]

I would love to hear from you all day!! with all due respect

Great content as always

[vaishalibhalerao]

Thanks for sharing. It was helpful. Thanks.

[prakashbaravkar]

How to convert the image binary form to jpg file ?

[avinashc]

Give numbers to videos that would be better to search if we want again

[mainlykanchan]

Hello ma'am
Mere pc(mysql) me charindex work nhi krv rha h . Plz help

[jamesgg]

If you have a string with multiple lines and you want to select each line as its own row, a recursive CTE is the way to do it. I.E:

DECLARE @needle VARCHAR(1000), @data VARCHAR(100);

SET @needle = '.'; -- What character separates lines?
SET @data = 'This is line 1.This is line 2.This is line 3.This is line 4.This is line 5.';


WITH dataLines( lineNum, lineEnd ) AS (
SELECT 0, 0
UNION ALL
SELECT 1 + lineNum, CHARINDEX( @needle, @data, 1 + lineEnd )
FROM dataLines
WHERE lineNum = 0 OR lineEnd != 0 )


SELECT b.lineNum as 'lineNum',
SUBSTRING( @data, 1 + a.lineEnd, b.lineEnd - a.lineEnd ) as 'dataLine'
FROM dataLines a
LEFT JOIN dataLines b
ON 1 + a.lineNum = b.lineNum
WHERE b.lineEnd != 0