Identify new line or carriage return in string column in SQL Server -

Sometimes we need to visually identify new line or carriage return in string column in SQL Server. But it is not possible to do that by using SELECT statement. Let us see an example about that and how we could easily do that.

--create a sample table
if OBJECT_ID('tempdb.sys.#table1','U') is not null
	drop table #table1;
create table #table1(
	col1 int not null identity,
	col2 varchar(max)
);	
 
 
--insert samle text with no carriage return
 
insert into #table1 (col2)
select 'The quick, brown fox jumps over a lazy dog. DJs flock by when MTV ax quiz prog.';
 
--insert samle text with carriage return
 
insert into #table1 (col2)
select 'The quick, brown fox jumps over a lazy dog.' +
CHAR(10) +  
'DJs flock by when MTV ax quiz prog.';

If we simply select the table we won’t be able to identify if there is a new line in the text or now.

select * from #table1;

The best way to visually check if there is a new line or not is using PRINT function.

declare @var1 nvarchar(max);
 
select @var1 = col2 from #table1 where col1=1;	  
print @var1;

The above result is good since we don’t have any new line character there it is fine. Now let us check it for the second row that has carriage return

declare @var2 nvarchar(max);
 
select @var2 = col2 from #table1 where col1=2;	  
print @var2;

We could see that the text come in two lines.
If we want to remove a new line from a column we could use the revers method we did above as

--remove new line or carriage return
 
update #table1 set col2 = replace(col2,CHAR(10),'') where col1=2;

Now let us check if we acually removed a new line from our column.

declare @var3 nvarchar(max);
 
select @var3 = col2 from #table1 where col1=2;	  
print @var3;

As you can see above we manage to remove the new line from row 2 and this concludes our experiment.