SQL Server 2014

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
-- Create a numbers table 1-200 using Itzik Ben-Gan's row generator
WITH
  L0   AS (SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
SELECT
    -- Destination column type integer NOT NULL
    ISNULL(CONVERT(integer, N.n), 0) AS n
INTO dbo.Numbers
FROM Nums AS N
WHERE N.n >= 1
AND N.n <= 200
OPTION (MAXDOP 1);
200 rows affected

-- Add clustered primary key
ALTER TABLE dbo.Numbers
ADD CONSTRAINT PK_Numbers_n
PRIMARY KEY CLUSTERED (n)
WITH (SORT_IN_TEMPDB = ON, MAXDOP = 1, FILLFACTOR = 100);
DECLARE @T table
(
    SampleID varchar(200) NOT NULL
);

INSERT @T
    (SampleID)
VALUES
    ('ABC123124_A12312'),
    ('ABC123_A1212'),
    ('ABC123124_B12312'),
    ('AC123124_AD12312'),
    ('A12312_123'),
    ('999ABC888DEF');

set statistics xml on;

SELECT
    T.SampleID,
    Pattern =
        -- Initial star if the first character is numeric
        CASE WHEN LEFT(Bin.string, 1) LIKE '[0-9]' THEN N'*' ELSE N'' END +
        -- String aggregate non-numeric sections with a star delimiter
        ISNULL
        (
            STUFF
            (
                (
                    SELECT
                        [text()] = 
                            '*' + 
                            SUBSTRING(Bin.string, GroupStart.pos, GroupEnd.pos - GroupStart.pos)
                    FROM
                    (
                        -- Start position of a non-numeric segemnt
                        SELECT
                            N.n
                        FROM dbo.Numbers AS N
                        WHERE
                            N.n BETWEEN 1 AND Bin.chars
                            AND SUBSTRING(Bin.string, N.n, 1) LIKE '[^0-9]'
                            AND (N.n = 1 OR SUBSTRING(Bin.string, N.n - 1, 1) LIKE '[0-9]')
                    ) AS GroupStart (pos)
                    CROSS APPLY 
                    (
                        -- End position of the non-numeric segment
                        SELECT TOP (1)
                            N.n
                        FROM dbo.Numbers AS N
                        WHERE
                            N.n BETWEEN GroupStart.pos AND Bin.chars + 1
                            AND (N.n = Bin.chars + 1 OR SUBSTRING(Bin.string, N.n, 1) LIKE '[0-9]')
                        ORDER BY 
                            N.n ASC
                    ) AS GroupEnd (pos)
                    ORDER BY
                        GroupStart.pos
                    FOR XML PATH ('')
                )
                -- Remove the initial delimiter
                , 1, 1, ''
            )
        , N''
        ) +
        -- Final star if the last character is numeric
        CASE 
            WHEN RIGHT(Bin.string, 1) LIKE '[0-9]' 
            AND Bin.string LIKE N'%[^0-9]%'
                THEN N'*' 
                ELSE N''
        END
FROM @T AS T
OUTER APPLY 
    (
        VALUES
            (
                -- Use binary collation for speed
                T.SampleID COLLATE Latin1_General_100_BIN2,
                LEN(T.SampleID)
            )
    ) AS Bin (string, chars);


SampleID
Pattern


ABC123124_A12312
ABC*_A*

ABC123_A1212
ABC*_A*

ABC123124_B12312
ABC*_B*

AC123124_AD12312
AC*_AD*

A12312_123
A*_*

999ABC888DEF
*ABC*DEF




Microsoft SQL Server 2005 XML Showplan


<ShowPlanXML xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan" Version="1.480" Build="12.0.6293.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa;    T.SampleID,&#xa;    Pattern =&#xa;        -- Initial star if the first character is numeric&#xa;        CASE WHEN LEFT(Bin.string, 1) LIKE &apos;[0-9]&apos; THEN N&apos;*&apos; ELSE N&apos;&apos; END +&#xa;        -- String aggregate non-numeric sections with a star delimiter&#xa;        ISNULL&#xa;        (&#xa;            STUFF&#xa;            (&#xa;                (&#xa;                    SELECT&#xa;                        [text()] = &#xa;                            &apos;*&apos; + &#xa;                            SUBSTRING(Bin.string, GroupStart.pos, GroupEnd.pos - GroupStart.pos)&#xa;                    FROM&#xa;                    (&#xa;                        -- Start position of a non-numeric segemnt&#xa;                        SELECT&#xa;                            N.n&#xa;                        FROM dbo.Numbers AS N&#xa;                        WHERE&#xa;                            N.n BETWEEN 1 AND Bin.chars&#xa;                            AND SUBSTRING(Bin.string, N.n, 1) LIKE &apos;[^0-9]&apos;&#xa;                            AND (N.n = 1 OR SUBSTRING(Bin.string, N.n - 1, 1) LIKE &apos;[0-9]&apos;)&#xa;                    ) AS GroupStart (pos)&#xa;                    CROSS APPLY &#xa;                    (&#xa;                        -- End position of the non-numeric segment&#xa;                        SELECT TOP (1)&#xa;                            N.n&#xa;                        FROM dbo.Numbers AS N&#xa;                        WHERE&#xa;                            N.n BETWEEN GroupStart.pos AND Bin.chars + 1&#xa;                            AND (N.n = Bin.chars + 1 OR SUBSTRING(Bin.string, N.n, 1) LIKE &apos;[0-9]&apos;)&#xa;                        ORDER BY &#xa;                            N.n ASC&#xa;                    ) AS GroupEnd (pos)&#xa;                    ORDER BY&#xa;                        GroupStart.pos&#xa;                    FOR XML PATH (&apos;&apos;)&#xa;                )&#xa;                -- Remove the initial delimiter&#xa;                , 1, 1, &apos;&apos;&#xa;            )&#xa;        , N&apos;&apos;&#xa;        ) +&#xa;        -- Final star if the last character is numeric&#xa;        CASE &#xa;            WHEN RIGHT(Bin.string, 1) LIKE &apos;[0-9]&apos; &#xa;            AND Bin.string LIKE N&apos;%[^0-9]%&apos;&#xa;                THEN N&apos;*&apos; &#xa;                ELSE N&apos;&apos;&#xa;        END&#xa;FROM @T AS T&#xa;OUTER APPLY &#xa;    (&#xa;        VALUES&#xa;            (&#xa;                -- Use binary collation for speed&#xa;                T.SampleID COLLATE Latin1_General_100_BIN2,&#xa;                LEN(T.SampleID)&#xa;            )&#xa;    ) AS Bin (string, chars)" StatementId="1" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.0106442" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0xA47D89855C1B6167" QueryPlanHash="0x40CCBD3B44117BF6" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120"><StatementSetOptions QUOTED_IDENTIFIER="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" NUMERIC_ROUNDABORT="false"/><QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="NoParallelPlansInDesktopOrExpressEdition" CachedPlanSize="48" CompileTime="0" CompileCPU="0" CompileMemory="680"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="838808" EstimatedPagesCached="52425" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="1465728"/><TraceFlags IsCompileTime="1"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><TraceFlags IsCompileTime="0"><TraceFlag Value="8017" Scope="Global"/></TraceFlags><QueryTimeStats ElapsedTime="0" CpuTime="0"/><RelOp NodeId="0" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="4137" EstimatedTotalSubtreeCost="0.0106442" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/><ColumnReference Column="Expr1009"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1009"/><ScalarOperator ScalarString="CASE WHEN substring([Expr1002],(1),(1)) like &apos;[0-9]&apos; THEN N&apos;*&apos; ELSE N&apos;&apos; END+isnull(stuff([Expr1007],(1),(1),N&apos;&apos;),CONVERT_IMPLICIT(nvarchar(max),N&apos;&apos;,0))+CASE WHEN right([Expr1002],(1)) like &apos;[0-9]&apos; AND CONVERT_IMPLICIT(nvarchar(200),[Expr1002],0) like N&apos;%[^0-9]%&apos; THEN N&apos;*&apos; ELSE N&apos;&apos; END"><Arithmetic Operation="ADD"><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><IF><Condition><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0-9]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="N&apos;*&apos;"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="N&apos;&apos;"/></ScalarOperator></Else></IF></ScalarOperator><ScalarOperator><Intrinsic FunctionName="isnull"><ScalarOperator><Intrinsic FunctionName="stuff"><ScalarOperator><Identifier><ColumnReference Column="Expr1007"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator><ScalarOperator><Const ConstValue="N&apos;&apos;"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Identifier><ColumnReference Column="ConstExpr1010"><ScalarOperator><Convert DataType="nvarchar(max)" Length="2147483647" Style="0" Implicit="1"><ScalarOperator><Const ConstValue="N&apos;&apos;"/></ScalarOperator></Convert></ScalarOperator></ColumnReference></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="AND"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Intrinsic FunctionName="right"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0-9]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Convert DataType="nvarchar" Length="400" Style="0" Implicit="1"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator><Const ConstValue="N&apos;%[^0-9]%&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="N&apos;*&apos;"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="N&apos;&apos;"/></ScalarOperator></Else></IF></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="4239" EstimatedTotalSubtreeCost="0.0106441" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/><ColumnReference Column="Expr1002"/><ColumnReference Column="Expr1007"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Column="Expr1002"/><ColumnReference Column="Expr1003"/></OuterReferences><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="217" EstimatedTotalSubtreeCost="0.00328844" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/><ColumnReference Column="Expr1002"/><ColumnReference Column="Expr1003"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></OuterReferences><RelOp NodeId="3" PhysicalOp="Table Scan" LogicalOp="Table Scan" EstimateRows="1" EstimateIO="0.003125" EstimateCPU="0.0001581" AvgRowSize="111" EstimatedTotalSubtreeCost="0.0032831" TableCardinality="0" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualScans="1" ActualLogicalReads="1" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="6" ActualEndOfScans="1" ActualExecutions="1"/></RunTimeInformation><TableScan Ordered="0" ForcedIndex="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></DefinedValue></DefinedValues><Object Table="[@T]" Alias="[T]" Storage="RowStore"/></TableScan></RelOp><RelOp NodeId="4" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="115" EstimatedTotalSubtreeCost="1.157e-006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"/><ColumnReference Column="Expr1003"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"/></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="CONVERT(varchar(200),@T.[SampleID] as [T].[SampleID],0)"><Convert DataType="varchar" Length="200" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></Identifier></ScalarOperator></Convert></ScalarOperator><ScalarOperator ScalarString="len(@T.[SampleID] as [T].[SampleID])"><Intrinsic FunctionName="len"><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></Row></Values></ConstantScan></RelOp></NestedLoops></RelOp><RelOp NodeId="7" PhysicalOp="UDX" LogicalOp="UDX" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.00735146" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1007"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"/></RunTimeInformation><Extension UDXName="FOR XML"><DefinedValues><DefinedValue><ColumnReference Column="Expr1007"/></DefinedValue></DefinedValues><UsedUDXColumns><ColumnReference Column="Expr1006"/><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></UsedUDXColumns><RelOp NodeId="8" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="4.04671" EstimateIO="0" EstimateCPU="4.04671e-007" AvgRowSize="115" EstimatedTotalSubtreeCost="0.00735046" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/><ColumnReference Column="Expr1006"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"/><ScalarOperator ScalarString="&apos;*&apos;+substring([Expr1002],[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n],[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n]-[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n])"><Arithmetic Operation="ADD"><ScalarOperator><Const ConstValue="&apos;*&apos;"/></ScalarOperator><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator></Arithmetic></ScalarOperator></Intrinsic></ScalarOperator></Arithmetic></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="9" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="4.04671" EstimateIO="0" EstimateCPU="1.69152e-005" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00735005" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="6" ActualExecutions="6"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OuterReferences><RelOp NodeId="11" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="4.04671" EstimateIO="0.003125" EstimateCPU="0.000223" AvgRowSize="11" EstimatedTotalSubtreeCost="0.003348" TableCardinality="200" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="82" ActualEndOfScans="6" ActualExecutions="6"/></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></DefinedValue></DefinedValues><Object Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Index="[PK_Numbers_n]" Alias="[N]" TableReferenceId="1" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1003]"><Identifier><ColumnReference Column="Expr1003"/></Identifier></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="substring([Expr1002],[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n],(1)) like &apos;[^0-9]&apos; AND ([fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n]=(1) OR substring([Expr1002],[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n]-(1),(1)) like &apos;[0-9]&apos;)"><Logical Operation="AND"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[^0-9]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Arithmetic Operation="SUB"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0-9]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp><RelOp NodeId="13" PhysicalOp="Top" LogicalOp="Top" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-007" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00381193" Parallel="0" EstimateRebinds="3.04671" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="12"/></RunTimeInformation><Top RowCount="0" IsPercent="0" WithTies="0"><TopExpression><ScalarOperator ScalarString="(1)"><Const ConstValue="(1)"/></ScalarOperator></TopExpression><RelOp NodeId="14" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="1" EstimateRowsWithoutRowGoal="7.48115" EstimateIO="0.003125" EstimateCPU="0.00019315" AvgRowSize="11" EstimatedTotalSubtreeCost="0.00377989" TableCardinality="200" Parallel="0" EstimateRebinds="3.04671" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="12" ActualElapsedms="0" ActualCPUms="0" ActualScans="12" ActualLogicalReads="24" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="40" ActualEndOfScans="0" ActualExecutions="12"/></RunTimeInformation><IndexScan Ordered="1" ScanDirection="FORWARD" ForcedIndex="0" ForceSeek="0" ForceScan="0" NoExpandHint="0" Storage="RowStore"><DefinedValues><DefinedValue><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></DefinedValue></DefinedValues><Object Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Index="[PK_Numbers_n]" Alias="[N]" TableReferenceId="2" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n]"><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator></RangeExpressions></StartRange><EndRange ScanType="LE"><RangeColumns><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="[Expr1003]+(1)"><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Arithmetic></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates><Predicate><ScalarOperator ScalarString="[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n]=([Expr1003]+(1)) OR substring([Expr1002],[fiddle_2548d7757c974aab9e41ed472813c760].[dbo].[Numbers].[n] as [N].[n],(1)) like &apos;[0-9]&apos;"><Logical Operation="OR"><ScalarOperator><Compare CompareOp="EQ"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Column="Expr1003"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Arithmetic></ScalarOperator></Compare></ScalarOperator><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_2548d7757c974aab9e41ed472813c760]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0-9]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Predicate></IndexScan></RelOp></Top></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Extension></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SampleID	Pattern
ABC123124_A12312	ABC_A
ABC123_A1212	ABC_A
ABC123124_B12312	ABC_B
AC123124_AD12312	AC_AD
A12312_123	A_
999ABC888DEF	ABCDEF