T-SQL split response header from SP-CodePudding

Within a T-SQL script I am calling a stored procedure, this stored procedure handles HTTP requests and is returning stuff like HTTP status code, status text and response headers.

The stored procedure is an 3rd party managed/created SP which I cannot edit (because when the vendor is updating their application the SP will be overwritten). So I have to deal with the output of the SP.

The response header which I am receiving after the HTTP call is made is a full string of all the headers (seperated by two spaces ' ').

This is the response header:

Cache-Control: no-cache,no-store,max-age=0  Connection: keep-alive  Date: Thu, 10 Feb 2022 07:46:23 GMT  Transfer-Encoding: chunked  Via: 1.1 <snip>.cloudfront.net (CloudFront)  Content-Type: application/json  Notification-Box-Id: 627ac06d-snip-snip-snip-7ae2cda3f8ae  Notification-Message-Id: c6ef44cd-snip-snip-snip-be196a48751b  X-Envoy-Upstream-Service-Time: 105  Strict-Transport-Security: max-age=31536000;  Content-Security-Policy: default-src 'self'  Strict-Transport-Security: max-age=31536000; includeSubDomains; preload  X-Cache: Miss from cloudfront  X-Amz-Cf-Pop: FRA6-C1  X-Amz-Cf-Id: 61b-snip-snipsnipsnip-vpg==

I need to have the value of two response headers (Notification-Box-Id & Notification-Message-Id) from this string. I've tried to use STRING_SPLIT but it does not accept two spaces (because of nchar(1)).

My assumption is that the format is always the same, so with LEFT(), RIGHT() and LEN() I can probably manage it to work. But is this the way to go? Is there any other option which is less messy?

CodePudding user response：

This is quite easy. Grab a copy of delimitedSplit8k. It returns the item and it's ordinal position in the string. And it's fast.

DECLARE @x VARCHAR(1000) = 'Cache-Control: no-cache,no-store,max-age=0  Connection: keep-alive  Date: Thu, 10 Feb 2022 07:46:23 GMT  Transfer-Encoding: chunked  Via: 1.1 <snip>.cloudfront.net (CloudFront)  Content-Type: application/json  Notification-Box-Id: 627ac06d-snip-snip-snip-7ae2cda3f8ae  Notification-Message-Id: c6ef44cd-snip-snip-snip-be196a48751b  X-Envoy-Upstream-Service-Time: 105  Strict-Transport-Security: max-age=31536000;  Content-Security-Policy: default-src ''self''  Strict-Transport-Security: max-age=31536000; includeSubDomains; preload  X-Cache: Miss from cloudfront  X-Amz-Cf-Pop: FRA6-C1  X-Amz-Cf-Id: 61b-snip-snipsnipsnip-vpg==    ';

SELECT split.*
FROM
(
  SELECT split.Item, Val = LEAD(split.Item,1) OVER (ORDER BY split.ItemNumber)
  FROM DW.dbo.DelimitedSplit8K(@x, '') AS split
) AS split(Item,ItemValue)
WHERE split.Item LIKE '%Notification-Box-Id%'
 OR   split.Item LIKE '%Notification-Message-Id%';

Returns:

Item                      ItemValue
------------------------- -----------------------------------------
Notification-Box-Id:      627ac06d-snip-snip-snip-7ae2cda3f8ae
Notification-Message-Id:  c6ef44cd-snip-snip-snip-be196a48751b