question

This is very easy to do in T-SQL. You're just missing one of the more important tools, a "Tally Table" or a "Tally Function". Here's the code to build a "Tally Function". Most of the code is documentation. CREATE FUNCTION [dbo].[fnTally] /****************************************************************************** Purpose: Basically, this function can be used in place of either a zero based or a unit (1) based Tally table. More formally, given a starting value of either 0 or 1 and a maximum value of up to 1 Trillion, returns a sequence of numbers from the starting value up to and including the maximum value. ------------------------------------------------------------------------------- Usage Example(s): --===== Return a count of 0 to 1000 SELECT N FROM dbo.fnTally(0,1000); --1001 values 0 thru 1000 --===== Return a count of 1 to 1000 SELECT N FROM dbo.fnTally(1,1000); --1000 values 1 thru 1000 ------------------------------------------------------------------------------- Programmer's Notes: 1. This is a high performance Inline Table Valued Function (iTVF). 2. This function produces exactly 0 reads and 0 writes. 3. Truth Table: IF @BaseValue 0 or IS NULL, then the first value will be a 1. IF @BaseValue = 0, then the first value will be a 0. IF @MaxValue is NULL Then error 'The number of rows in the TOP clause must be an integer.' will be returned. IF @MaxValue > 1 Trillion Then silent truncation to 1 Trillion will occur. IF @BaseValue = 0 and... @MaxValue = 0 then 1 row with 0 returned. @MaxValue > 0 then 1 row with 0 and sequence 1 to @MaxValue returned. @MaxValue < 0 then error "A TOP N value may not be negative.". IF @BaseValue 1 and ... @MaxValue = 0 then no rows returned. @MaxValue > 0 then sequence 1 to @MaxValue returned. @MaxValue < 0 then error "A TOP N value may not be negative.". ------------------------------------------------------------------------------- Credits: Original "cascading CTE" (cCte) created by Itzik Ben-Gan and has been modified to suit this function and to increease performance. ------------------------------------------------------------------------------- Revision History: Rev 00 - Date Unknown - Jeff Moden - Initial creation and test. Rev 01 - Date Unknown - Jeff Moden - Add the "TOP" optimization. Rev 02 - 23 Jul 2013 - Jeff Moden - Added features and renamed the function. 1. Added ability to start at 0 or 1 (Base value). 2. Converted to allow an insanely large (1 trillion) max number. 3. Intentionally kept at a 2005 level for legacy systems. ******************************************************************************/ --===== Define the I/O for this function (@BaseValue BIT, @MaxValue INT) RETURNS TABLE WITH SCHEMABINDING AS RETURN WITH --===== Generate up to 1 Trillion rows ("En" indicates the power of 10 produced) E1(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1), E4(N) AS (SELECT 1 FROM E1 a,E1 b,E1 c,E1 d), E12(N) AS (SELECT 1 FROM E4 a,E4 b,E4 c) --===== If @BaseValue = 0 then create a zero row SELECT N = 0 WHERE @BaseValue = 0 UNION ALL --===== Enumerate the rows generated by the cascading CTEs (cCTE) SELECT TOP (@MaxValue) N = ROW_NUMBER() OVER (ORDER BY N) FROM E12 ; After that, your problem becomes both very easy and quite fast. DECLARE @Original VARCHAR(8000); SELECT @Original = 'A1B-this is a test A1D- this is the second part'; SELECT ExtractedValue = SUBSTRING(@Original,t.N,3) ,Original = @Original FROM dbo.fnTally(1,LEN(@Original)) t WHERE SUBSTRING(@Original,t.N,4) LIKE '[A-Z][0-9][A-Z][-]' ; If you'd like to see how to apply that to a whole table, please provide the CREATE TABLE code for the example and some readily consumable data in the form of INSERTS and I'll be happy to demonstrate that, as well.