To split out the pd_id's you could try STRING_SPLIT to separate based on space delimiter.  Since some 'PD-[0-9]' values are encompassed by other extra characters a conditional substring is done...

• This reply was modified 3 years, 4 months ago by Steve Collins.

Thanks to Jeff Moden in this thread for the function used here

In row 1 of the expected output from which input table does the Rnge value 45 come from? ...

• This reply was modified 3 years, 4 months ago by Steve Collins.

THAT, good Sir, is a beautiful thing!  All you need to do now is give the author of that good function a little credit by citing the article where...

Afaik this actually produces the desired output.  Should work with SQL 2014.  It uses the ordinal splitter DelimitedSplit8K_Lead.  The query: splits the input string on '_' underscore, finds the offset...

Instead of a WHILE loop you could try a numbers table or tally function approach

declare @string varchar(255) = 'EMA_20210526T211254_0000_MRNMM0000001240_PMS110460PAT000001287_PID15235307_OR_Dr';

select string_agg(v.chr, '') within group (order by fn.n)...

These two statements are equivalent afaik.  Beginning with [Edit] 2022 (or compatibility level 150 in Azure SQL) they added the GREATEST function.  Prior ways were UNPIVOT using the built-in verb...

• This reply was modified 3 years, 4 months ago by Steve Collins.

The value 'calc.adjusted_minutes' is referenced twice in the...

It appears to be a nonworking attempt at a catch all query.  Imo the easy answer is two procedures or however many are necessary.  In this case imo the OP...

Since the current code uses REPLACE with strings of equal length followed by LEFT(8), it's equivalent to SUBSTRING(input_string, 38, 8)

select v.in_str, substring(v.in_str, 38, 8) inv_num
from (values ('Company...

You could try the PATINDEX function and look for the first occurrence of 8 consecutive integers enclosed in parentheses.  Instead of typing the criteria 8 times this uses REPLICATE.  The...

• This reply was modified 3 years, 4 months ago by Steve Collins.

select cast(year(v.sort_mo) as smallint), input_str.mo
from (values ('Jan'),('Feb'),('Mar'),
             ('Apr'),('May'),('Jun'),
       ...

The data types were added.  It says:...

You could try something like this

with rn_cte as (
    select *, case when row_number() over (partition by [Itinerary] order by [CreationDate])=1 and LegSegment is...

Here's 1, 3, and 4

--(1)
select v.sort_mo, input_str.mo
from (values ('Jan'),('Feb'),('Mar')) input_str(mo)
     cross apply (values (parse(concat(input_str.mo, '-01-1900') as date))) v(sort_mo)
order by v.sort_mo;

--(3)
declare @b  ...

It's an interesting approach Drew.  As I see it it's a partial self-CROSS JOIN then summarize instead of summarize then index JOIN.  My suspicion was confirmed using Jeff's test harness...

• This reply was modified 3 years, 4 months ago by Steve Collins.