Scan Varchar Column, Extract values that follow a string

Transact-SQL
1

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.

2 

;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
3

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!

4

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
5

Thanks, @yosiasz

6

I encourage you to do disciplined self code review.

1 Like
7

hi

another way of doing this

a little of round about way

but :rofl: :thinking: :+1:

create tables data script

drop table #x
create table #x ( strng varchar(100))
drop table #y
create table #y(str1 varchar(20), id int identity(1,1))

insert into #x select 'CarrierProNumber: 52960 yyyyy rrr ttt '
insert into #x select 'xxxx 567 yyyyyyyy CarrierProNumber: 4567 zzzz'
insert into #x select '1234 rrr tt yyyy CarrierProNumber: 234'

insert into #y select value from #x cross apply string_split(strng,' ') 

; with cte as (select str1 , id from #y where str1 like '%CarrierProNumber:%')
    select 
	   a.str1+' '+b.str1 
   from 
     cte a join #y b on a.id = b.id-1

image
image733×695 11.3 KB