Get second line of text from text field

I have a table with a text field called REMARK. In this text field, there will be a minimum of two lines of text formatted like this:

TRAILER:123456
SEAL:987654

I need to split each line at the carriage return and have separate columns. I've got the first one working correctly (TRAILER) but SEAL is coming up NULL. I want to get the second line from the first character after the CHAR(13) through the end of the line or to the next CHAR(13).
SELECT H.PO_NUMBER, 
D.PRODUCT_CLASS +' '+ D.PRODUCT_TYPE AS TYPE, 
H.ORDER_DATE, 
H.VENDOR_ADDRESS_CODE AS DC,
H.VENDOR_NUMBER,
[dbo].[getVendorName](H.PO_NUMBER)+' - '+[dbo].[getVendorAddressCityState](H.PO_NUMBER) as WHO,
FREIGHT_CODE,
H.VENDOR_REFERENCE, 
'' AS SEAL,
H.REQUIRED_DATE, 
L.PART_CODE,
H.REMARK,
CASE WHEN CHARINDEX(CHAR(13), REMARK) = 0 THEN REMARK
	 ELSE SUBSTRING(REMARK, 0, CHARINDEX(CHAR(13), REMARK))
END AS TRAILER,
CASE WHEN CHARINDEX(CHAR(13), REMARK) = 1 THEN SUBSTRING(REMARK, CHARINDEX(CHAR(13), REMARK)+1,'END AT NEXT CHARINDEX(CHAR(13)')
END AS SEAL
FROM dbo.POP_HEADERS H INNER JOIN POP_LINES L
ON H.COMPANY_CODE=L.COMPANY_CODE
AND H.PO_NUMBER=L.PO_NUMBER RIGHT OUTER JOIN VW_PRODUCT_DATA D
ON L.COMPANY_CODE=D.COMPANY_CODE
AND L.PART_CODE=D.PART_CODE 
WHERE H.PO_NUMBER=158924

Open in new window

Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brendt HessSenior DBACommented:
I don't believe this CASE WHEN statement is formatted correctly.

CASE WHEN CHARINDEX(CHAR(13), REMARK) = 1

Open in new window

It is very unlikely that the first character in the REMARK text field is a CHAR(13).  Could you, perhaps, mean to be testing like this?

CASE WHEN CHARINDEX(CHAR(13), REMARK) >= 1

Open in new window

Remeber, CHARINDEX is not an existence test, it is a location test.  In your sample data, the CHARINDEX would return a value of 15, assuming no trailing spaces.  Since 15 <> 1, your CASE test will never return TRUE, and no value will be extracted for this field.

Notes on improving the extraction next...
0
Brendt HessSenior DBACommented:
To split up a field, you have several options.  The method you use above is, however, flawed. Assuming you implement the change of looking for CHARINDEX >=1, then the SUBSTRING will not return ONLY the Seal value in cases where there are more lines in the field.  Since I cannot guarantee this for the future, here is how I would probably code this using this style of splitting:

SELECT
	CASE 
		WHEN CHARINDEX(CHAR(13), @REMARK) BETWEEN 1 and Len(@Remark)-1
			-- don't try to split if there is  no data after a terminal CHAR(13) or CR/LF pair
			THEN SUBSTRING(RIGHT(@Remark, LEN(@Remark) - (CHARINDEX(CHAR(13), @REMARK)+1)), 1, CHARINDEX(CHAR(13), RIGHT(@Remark, LEN(@Remark) - (CHARINDEX(CHAR(13), @REMARK)+1)) + CHAR(13), -1))
	END AS SEAL

Open in new window

There are several changes in this code from your basic code to handle specific cases that can commonly happen.

1.

REMARKS contains a CR, but has no second data line
In this case, the code needs to check for the existence of actual data past the first CR or CR/LF pair.  This is handled in this query with the BETWEEN statement in the CASE condition.

2.

REMARKS contains two lines of data, but has no CR at the end of the second line.
This is handled by appending a CR at the end of the REMARKS field.  If there already is a CR in the data, no problem.  Otherwise, adding the CR means that there will likewise be no problem.To test the above code, I used the samples below to validate all of the basic cases I could think of immediately:

DECLARE @Remark varchar(MAX)

SET @Remark = 'TRAILER:123456
'

SET @Remark = 'TRAILER:123456
SEAL:987654'

SET @Remark = 'TRAILER:123456
SEAL:987654
'
SET @Remark = 'TRAILER:123456
SEAL:987654
FOO:BAR'

Open in new window

Another method of splitting the data can be used when you are processing one row only, whether it is a one-row-at-a-time batch (e.g. a cursor) or you are only processing a single row (e.g. a lookup and display process).  In this case, you can use a field splitting function to perform the split, and use the returned data as a table in your query.  There are hundreds of examples of this on the web, but if you are interested, I would recommend you look for a splitting function that makes use of a Tally table.  These versions are significantly faster than the other versions of splitter functions.
0
Scott PletcherSenior DBACommented:
I think using CROSS APPLY to compute row values makes this much easier to follow, and thus to change.  I avoid any CASE statements by concatenating CHAR(13)(s) to the end of the value if/as needed.  For example:

SELECT H.PO_NUMBER,
D.PRODUCT_CLASS +' '+ D.PRODUCT_TYPE AS TYPE,
H.ORDER_DATE,
H.VENDOR_ADDRESS_CODE AS DC,
H.VENDOR_NUMBER,
[dbo].[getVendorName](H.PO_NUMBER)+' - '+[dbo].[getVendorAddressCityState](H.PO_NUMBER) as WHO,
FREIGHT_CODE,
H.VENDOR_REFERENCE,
'' AS SEAL,
H.REQUIRED_DATE,
L.PART_CODE,
H.REMARK,
LEFT(H.REMARK, H2.REMARK_1ST_CHAR13 - 1) AS TRAILER,
SUBSTRING(H.REMARK, H2.REMARK_1ST_CHAR13 + 1, H2.REMARK_2ND_CHAR13 - H2.REMARK_1ST_CHAR13 - 1) AS SEAL

FROM dbo.POP_HEADERS H INNER JOIN POP_LINES L
ON H.COMPANY_CODE=L.COMPANY_CODE
AND H.PO_NUMBER=L.PO_NUMBER RIGHT OUTER JOIN VW_PRODUCT_DATA D
ON L.COMPANY_CODE=D.COMPANY_CODE
AND L.PART_CODE=D.PART_CODE
CROSS APPLY (
    SELECT
        CHARINDEX(CHAR(13), REMARK + CHAR(13)) AS REMARK_1ST_CHAR13,
        CHARINDEX(CHAR(13), REMARK + CHAR(13) + CHAR(13), CHARINDEX(CHAR(13), REMARK + CHAR(13)) + 1) AS REMARK_2ND_CHAR13
) AS H2

WHERE H.PO_NUMBER=158924


Here's a quick sample of the extract code in action:



SELECT
H.REMARK,
H2.REMARK_1ST_CHAR13,
H2.REMARK_2ND_CHAR13,
LEFT(H.REMARK, H2.REMARK_1ST_CHAR13 - 1) AS TRAILER,
SUBSTRING(H.REMARK, H2.REMARK_1ST_CHAR13 + 1, H2.REMARK_2ND_CHAR13 - H2.REMARK_1ST_CHAR13 - 1) AS SEAL
FROM (
    select 'TRAILER:123456' + CHAR(13) + 'SEAL:987654' as remark UNION ALL
    select 'TRAILER:123457' UNION ALL
    select 'TRAILER:123458' + CHAR(13) + 'SEAL:987654' + CHAR(13) + 'test' UNION ALL
    select 'TRAILER:123459' + CHAR(13) + 'SEAL:9876' + CHAR(13) + 'test a really long string after a shorter seal'
) AS H
CROSS APPLY (
    SELECT
        CHARINDEX(CHAR(13), REMARK + CHAR(13)) AS REMARK_1ST_CHAR13,
        CHARINDEX(CHAR(13), REMARK + CHAR(13) + CHAR(13), CHARINDEX(CHAR(13), REMARK + CHAR(13)) + 1) AS REMARK_2ND_CHAR13
) AS H2
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Carla RomereDirector of Information TechnologyAuthor Commented:
bhess1:

When I modified my code using what you sent (using DATALENGTH instead of LEN since it's a text field), I end up with this:
SELECT 
REMARK,
CASE CHARINDEX(CHAR(13), REMARK)
WHEN 0 THEN REMARK
ELSE LEFT(REMARK, CHARINDEX(CHAR(13), REMARK) - 1) 
END AS TRAILER,
CASE WHEN CHARINDEX(CHAR(13), REMARK) BETWEEN 1 and DATALENGTH(REMARK)-1
-- don't try to split if there is  no data after a terminal CHAR(13) or CR/LF pair
THEN SUBSTRING(RIGHT(REMARK, DATALENGTH(REMARK) - (CHARINDEX(CHAR(13), REMARK)+1)), 1, CHARINDEX(CHAR(13), RIGHT(REMARK, DATALENGTH(REMARK) - (CHARINDEX(CHAR(13), REMARK)+1)) + CHAR(13), -1))
END AS SEAL
FROM POP_HEADERS
WHERE PO_NUMBER=158924

Open in new window

and I get this error:
Msg 8116, Level 16, State 1, Line 1
Argument data type text is invalid for argument 1 of left function.

ScottPletcher:
When I run the code you sent, I get this error:
Msg 402, Level 16, State 1, Line 21
The data types text and char are incompatible in the add operator.
0
Scott PletcherSenior DBACommented:
Text ... yuck!

Anyway, this should do it (hopefully):


SELECT H.PO_NUMBER,
D.PRODUCT_CLASS +' '+ D.PRODUCT_TYPE AS TYPE,
H.ORDER_DATE,
H.VENDOR_ADDRESS_CODE AS DC,
H.VENDOR_NUMBER,
[dbo].[getVendorName](H.PO_NUMBER)+' - '+[dbo].[getVendorAddressCityState](H.PO_NUMBER) as WHO,
FREIGHT_CODE,
H.VENDOR_REFERENCE,
'' AS SEAL,
H.REQUIRED_DATE,
L.PART_CODE,
H.REMARK,
SUBSTRING(H.REMARK, 1, H2.REMARK_1ST_CHAR13 - 1) AS TRAILER,
SUBSTRING(H.REMARK, H2.REMARK_1ST_CHAR13 + 1, H2.REMARK_2ND_CHAR13 - H2.REMARK_1ST_CHAR13 - 1) AS SEAL
FROM dbo.POP_HEADERS H INNER JOIN POP_LINES L
ON H.COMPANY_CODE=L.COMPANY_CODE
AND H.PO_NUMBER=L.PO_NUMBER RIGHT OUTER JOIN VW_PRODUCT_DATA D
ON L.COMPANY_CODE=D.COMPANY_CODE
AND L.PART_CODE=D.PART_CODE
CROSS APPLY (
    SELECT
        CHARINDEX(CHAR(13), CAST(REMARK AS varchar(8000)) + CHAR(13)) AS REMARK_1ST_CHAR13,
        CHARINDEX(CHAR(13), CAST(REMARK AS varchar(8000)) + CHAR(13) + CHAR(13), CHARINDEX(CHAR(13), CAST(REMARK AS varchar(8000)) + CHAR(13)) + 1) AS REMARK_2ND_CHAR13
) AS H2
WHERE H.PO_NUMBER=158924
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Carla RomereDirector of Information TechnologyAuthor Commented:
That did it. I was even trying to use a temp table to convert to varchar(max) because this is an ERP system and I can't change the TEXT field to VARCHAR(MAX). This worked perfectly.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.