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,
    (
        SELECT
            CASE
                WHEN Chars.this NOT LIKE '[0123456789]' THEN Chars.this
                WHEN Chars.prev NOT LIKE '[0123456789]' THEN '*'
                ELSE ''
            END
        FROM dbo.Numbers AS N
        OUTER APPLY
        (
            SELECT 
                SUBSTRING(Bin.string, N.n, 1),
                SUBSTRING(Bin.string, N.n + 1, 1)
        ) AS Chars (prev, this)
        WHERE
            N.n BETWEEN 1 AND LEN(Bin.string)
        ORDER BY N.n
        FOR XML PATH ('')
    )
FROM @T AS T
OUTER APPLY (VALUES('$' + T.SampleID COLLATE Latin1_General_100_BIN2)) AS Bin (string);


SampleID
(No column name)


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.6024.0"><BatchSequence><Batch><Statements><StmtSimple StatementText="SELECT&#xa;    T.SampleID,&#xa;    (&#xa;        SELECT&#xa;            CASE&#xa;                WHEN Chars.this NOT LIKE &apos;[0123456789]&apos; THEN Chars.this&#xa;                WHEN Chars.prev NOT LIKE &apos;[0123456789]&apos; THEN &apos;*&apos;&#xa;                ELSE &apos;&apos;&#xa;            END&#xa;        FROM dbo.Numbers AS N&#xa;        OUTER APPLY&#xa;        (&#xa;            SELECT &#xa;                SUBSTRING(Bin.string, N.n, 1),&#xa;                SUBSTRING(Bin.string, N.n + 1, 1)&#xa;        ) AS Chars (prev, this)&#xa;        WHERE&#xa;            N.n BETWEEN 1 AND LEN(Bin.string)&#xa;        ORDER BY N.n&#xa;        FOR XML PATH (&apos;&apos;)&#xa;    )&#xa;FROM @T AS T&#xa;OUTER APPLY (VALUES(&apos;$&apos; + T.SampleID COLLATE Latin1_General_100_BIN2)) AS Bin (string)" StatementId="1" StatementCompId="4" StatementType="SELECT" RetrievedFromCache="true" StatementSubTreeCost="0.00695867" StatementEstRows="1" StatementOptmLevel="FULL" QueryHash="0x4A8EA874585ADA7E" QueryPlanHash="0x668A7322F0319087" 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="32" CompileTime="0" CompileCPU="0" CompileMemory="392"><MemoryGrantInfo SerialRequiredMemory="0" SerialDesiredMemory="0"/><OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="1258238" EstimatedPagesCached="78639" EstimatedAvailableDegreeOfParallelism="1" MaxCompileMemory="780248"/><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.00695867" 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="[Expr1007]"><Identifier><ColumnReference Column="Expr1007"/></Identifier></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="1" PhysicalOp="Nested Loops" LogicalOp="Inner Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="4137" EstimatedTotalSubtreeCost="0.00695857" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/><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"/></OuterReferences><RelOp NodeId="2" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="1" EstimateIO="0" EstimateCPU="4.18e-006" AvgRowSize="213" EstimatedTotalSubtreeCost="0.00328844" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/><ColumnReference Column="Expr1002"/></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="111" EstimatedTotalSubtreeCost="1.157e-006" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1002"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="6" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="6"/></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="&apos;$&apos;+CONVERT(varchar(200),@T.[SampleID] as [T].[SampleID],0)"><Arithmetic Operation="ADD"><ScalarOperator><Const ConstValue="&apos;$&apos;"/></ScalarOperator><ScalarOperator><Convert DataType="varchar" Length="200" Style="0" Implicit="0"><ScalarOperator><Identifier><ColumnReference Table="@T" Alias="[T]" Column="SampleID"/></Identifier></ScalarOperator></Convert></ScalarOperator></Arithmetic></ScalarOperator></Row></Values></ConstantScan></RelOp></NestedLoops></RelOp><RelOp NodeId="6" PhysicalOp="UDX" LogicalOp="UDX" EstimateRows="1" EstimateIO="0" EstimateCPU="1e-006" AvgRowSize="4035" EstimatedTotalSubtreeCost="0.00366596" 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_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></UsedUDXColumns><RelOp NodeId="7" PhysicalOp="Compute Scalar" LogicalOp="Compute Scalar" EstimateRows="60" EstimateIO="0" EstimateCPU="6e-006" AvgRowSize="15" EstimatedTotalSubtreeCost="0.00366496" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/><ColumnReference Column="Expr1006"/></OutputList><ComputeScalar><DefinedValues><DefinedValue><ColumnReference Column="Expr1006"/><ScalarOperator ScalarString="CASE WHEN NOT [Expr1005] like &apos;[0123456789]&apos; THEN [Expr1005] ELSE CASE WHEN NOT [Expr1004] like &apos;[0123456789]&apos; THEN &apos;*&apos; ELSE &apos;&apos; END END"><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Column="Expr1005"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0123456789]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Identifier><ColumnReference Column="Expr1005"/></Identifier></ScalarOperator></Then><Else><ScalarOperator><IF><Condition><ScalarOperator><Logical Operation="NOT"><ScalarOperator><Intrinsic FunctionName="like"><ScalarOperator><Identifier><ColumnReference Column="Expr1004"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="&apos;[0123456789]&apos;"/></ScalarOperator></Intrinsic></ScalarOperator></Logical></ScalarOperator></Condition><Then><ScalarOperator><Const ConstValue="&apos;*&apos;"/></ScalarOperator></Then><Else><ScalarOperator><Const ConstValue="&apos;&apos;"/></ScalarOperator></Else></IF></ScalarOperator></Else></IF></ScalarOperator></DefinedValue></DefinedValues><RelOp NodeId="8" PhysicalOp="Nested Loops" LogicalOp="Left Outer Join" EstimateRows="60" EstimateIO="0" EstimateCPU="0.0002508" AvgRowSize="17" EstimatedTotalSubtreeCost="0.00365896" Parallel="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/><ColumnReference Column="Expr1004"/><ColumnReference Column="Expr1005"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="88" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="6" ActualExecutions="6"/></RunTimeInformation><NestedLoops Optimized="0"><OuterReferences><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OuterReferences><RelOp NodeId="9" PhysicalOp="Clustered Index Seek" LogicalOp="Clustered Index Seek" EstimateRows="60" 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_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="88" ActualElapsedms="0" ActualCPUms="0" ActualScans="6" ActualLogicalReads="12" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" ActualRowsRead="88" 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_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></DefinedValue></DefinedValues><Object Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Index="[PK_Numbers_n]" Alias="[N]" IndexKind="Clustered" Storage="RowStore"/><SeekPredicates><SeekPredicateNew><SeekKeys><StartRange ScanType="GE"><RangeColumns><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" 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_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></RangeColumns><RangeExpressions><ScalarOperator ScalarString="len([Expr1002])"><Intrinsic FunctionName="len"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator></Intrinsic></ScalarOperator></RangeExpressions></EndRange></SeekKeys></SeekPredicateNew></SeekPredicates></IndexScan></RelOp><RelOp NodeId="10" PhysicalOp="Constant Scan" LogicalOp="Constant Scan" EstimateRows="1" EstimateIO="0" EstimateCPU="1.157e-006" AvgRowSize="13" EstimatedTotalSubtreeCost="6.0157e-005" Parallel="0" EstimateRebinds="59" EstimateRewinds="0" EstimatedExecutionMode="Row"><OutputList><ColumnReference Column="Expr1004"/><ColumnReference Column="Expr1005"/></OutputList><RunTimeInformation><RunTimeCountersPerThread Thread="0" ActualRows="88" ActualElapsedms="0" ActualCPUms="0" ActualEndOfScans="0" ActualExecutions="88"/></RunTimeInformation><ConstantScan><Values><Row><ScalarOperator ScalarString="substring([Expr1002],[fiddle_0ca1a83ab8bc4873833e875793c0d916].[dbo].[Numbers].[n] as [N].[n],(1))"><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator><ScalarOperator ScalarString="substring([Expr1002],[fiddle_0ca1a83ab8bc4873833e875793c0d916].[dbo].[Numbers].[n] as [N].[n]+(1),(1))"><Intrinsic FunctionName="substring"><ScalarOperator><Identifier><ColumnReference Column="Expr1002"/></Identifier></ScalarOperator><ScalarOperator><Arithmetic Operation="ADD"><ScalarOperator><Identifier><ColumnReference Database="[fiddle_0ca1a83ab8bc4873833e875793c0d916]" Schema="[dbo]" Table="[Numbers]" Alias="[N]" Column="n"/></Identifier></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Arithmetic></ScalarOperator><ScalarOperator><Const ConstValue="(1)"/></ScalarOperator></Intrinsic></ScalarOperator></Row></Values></ConstantScan></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></Extension></RelOp></NestedLoops></RelOp></ComputeScalar></RelOp></QueryPlan></StmtSimple></Statements></Batch></BatchSequence></ShowPlanXML>
SampleID	(No column name)
ABC123124_A12312	ABC_A
ABC123_A1212	ABC_A
ABC123124_B12312	ABC_B
AC123124_AD12312	AC_AD
A12312_123	A_
999ABC888DEF	ABCDEF