Based on Condition Extract Values Right of String

I have a dataset in sql that looks like this:

    Col1    Col2
 13_DND_       5
   _DND_       6
123_ETL_      10
    ETL_      12
    DND_      15
   _ETL_      17

If Col1 contains either _DND_ or _ETL_, I want to remove everything to the left of DND_ and ETL_.

Final expected output is below:

        Col1    Col2         Col3
  13_DND_456       5      DND_456
   _DND_de1f       6     DND_de1f
123_ETL_mene      10     ETL_mene
    ETL_test      12     ETL_test 
      DND_se      15       DND_se
   _ETL_def_      17     ETL_def_

I tried below for 1 condition but the result was NULL:

SELECT *, CASE WHEN Col1 LIKE '%_DND_%' 
THEN RIGHT(Col1, LENGTH(Col1) - CHARINDEX('DND_', Col1)) ELSE Col1 END Col3;

Answer

I would be inclined to use STUFF():

select (case when col1 like '%[_]DND%'
             then stuff(col1, 1, charindex('_DND', col1) - 1, '')
             when col1 like '%[_]END%'
             then stuff(col1, 1, charindex('_END', col1) - 1, '')
             else col1
        end) as col3

Your code doesn’t work because the LIKE fails unless “DND” starts at the second position. So, it is returning NULL.

Because _ is a wildcard in LIKE, the LIKE pattern escapes it.