clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 818545 fiddles created (9195 in the last week).

-- 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
hidden batch(es)

-- 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);
hidden batch(es)

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*