Select only the first character string before CR / LF from the text column

Question

Can I select or fine-tune only the first character string in a SQL Server text column to add the first character string to another text box in another table?

Answer 1

If you are using SQL Server 2005 or higher:

In the LEFT command, use CHARINDEX on CHAR (13) to find the position of the character of the first line, 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 also suggest, if possible, updating TEXT data types to varchar (max). varchar (max) is much more reliable.

Answer 2

Yes, do substringeither leftbefore the first new line of the text field.

Answer 3

.

SELECT  ( CASE WHEN CHARINDEX(CHAR(13), action_Item.Description) = 0
           THEN action_Item.Description
           ELSE SUBSTRING(action_Item.Description, 0,
                          CHARINDEX(CHAR(13), action_Item.Description))
      END ) AS [Description] FROM    action_Item

, "" "action_Item"

Answer 4

DECLARE @crlf   char(2);

SET     @crlf = CHAR(13) + CHAR(10);

UPDATE  table1
SET     LEFT(table2.fieldWithCRLF, CHARINDEX(table2.fieldWithCRLF, @crlf, 0) - 1) + table1.fieldToPrepend
FROM    table1
        INNER JOIN table2
            ON  table1.sharedKey = table2.sharedKey
WHERE   CHARINDEX(table2.fieldWithCRLF, @crlf, 0) > 0