Select just first line of chars up to CR/LF from a text column

Question

Is it possible to select or substring just the first line of chars in a SQL Server text column, to then prepend as the first line of chars in another text field in another table?

Answer 1

If you are running SQL Server 2005 or higher:

In the LEFT command, use CHARINDEX on CHAR(13) to find the position of the first line feed character, as in the following example:

declare @a table(id int identity(1,1) not null, lines text); --Source
declare @b table(id int identity(1,1) not null, lines text); --Target

insert into @a(lines) values ('1111111'+char(13)+char(10)+'222222')
insert into @b(lines) values ('aaaaa');

update b
set lines=LEFT(cast(a.lines as varchar(max)),CHARINDEX(char(13),cast(a.lines as varchar(max)),1)-1)+cast(b.lines as varchar(max))
from @a a
join @b b on a.id=b.id;

select * from @b;

I suggest also updating your TEXT data types to varchar(max), if possible. varchar(max) is much more robust.

Answer 2

Yes, do a substring or left till the first newline of the text field.