SQLTeam.com | Weblogs | Forums

Scan Varchar Column, Extract values that follow a string

Table HTMLSummary contains column ImportSummaryHTML defined as varchar(max) not nullable.

I need to "scan" the column and extract the value that follows string CarrierProNumber:1 space

i.e. it would extract 930452960 if the column contains
CarrierProNumber: 930452960
And CarrierProNumber is not always in the same position within the column

Thanks for any tips.


;WITH sample_data AS (
    SELECT 'xxx CarrierProNumber: 930452960 uu' AS ImportSummaryHTML
    UNION ALL
    SELECT 'aa bbb ccCarrier dd CarrierProNumber: 1234 xx yyy' AS ImportSummaryHTML
)
SELECT 
    ImportSummaryHTML,
    CASE WHEN start_byte = 0 THEN '' ELSE SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1, 
        PATINDEX('%[^0-9]%', SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1, 100) + ';') - 1) END AS value
FROM sample_data
CROSS APPLY (
    SELECT CHARINDEX('CarrierProNumber: ', ImportSummaryHTML) AS start_byte
) AS ca1
1 Like

Thank you, @ScottPletcher
I retrofitted my table into your code and came up with this:
SELECT ID,
ImportComments,
ImportSummaryHTML,
CASE WHEN start_byte = 0 THEN '' ELSE SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1,
PATINDEX('%[^0-9]%', SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1, 100) + ';') - 1) END AS CarrierProNmber
FROM [Glass].[dbo].[ShipmentImportFileDataRows]
CROSS APPLY (
SELECT CHARINDEX('CarrierProNumber: ', ImportSummaryHTML) AS start_byte
)
AS ca1
Works perfectly, but I need to add a Where clause.

SELECT ID,
ImportComments,
ImportSummaryHTML,
CASE WHEN start_byte = 0 THEN '' ELSE SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1,
PATINDEX('%[^0-9]%', SUBSTRING(ImportSummaryHTML, start_byte + LEN('CarrierProNumber: ') + 1, 100) + ';') - 1) END AS CarrierProNmber
FROM [Glass].[dbo].[ShipmentImportFileDataRows]
where ImportComments LIKE ('Skipped as Duplicate%')
CROSS APPLY (
SELECT CHARINDEX('CarrierProNumber: ', ImportSummaryHTML) AS start_byte
)
AS ca1

The Where clause causes errors
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'CROSS'.
Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

How can I incorporate the Where clause into this block?
Thanks!

You dont join or cross apply after where clause.

FROM [Glass].[dbo].[ShipmentImportFileDataRows]
CROSS APPLY (
SELECT CHARINDEX('CarrierProNumber: ', ImportSummaryHTML) AS start_byte
)
AS ca1
where ImportComments LIKE ('Skipped as Duplicate%')

1 Like

Thanks, @yosiasz

I encourage you to do disciplined self code review.

1 Like