It seems you're looking for the COALESCE function.  If the empty values are stored as '' then NULLIF returns NULL.  COALESCE returns the first (left to right of the parameter...

There are a number of issues with the code provided imo.  Minor issues are DISTINCT is not necessary when used in conjunction with GROUP BY and inconsistent or absent table...

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, 6 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, 6 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, 6 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, 6 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...