- Home
- Anonymous
- Sign in
- Create
- Ask a question
- Spaces
- Site Issues (NOT FOR DATABASE QUESTIONS)
- Explore
- Topics
- Questions
- Users
- Badges

I have the below string and want to extract from it using PATINDEX. this is my string "A1B-this is a test A1D- this is the second part" I want it produce these results: ExtractedValue Original A1B A1B-this is a test A1D- this is the second part A1D A1B-this is a test A1D- this is the second part This is the query i started working with, but need to get it to loop through the string to extract the second part as well. SELECT t.Original, SUBSTRING(t.Original,PATINDEX('%[A-Z][0-9][A-Z,-]%', T.Original),3) AS ExtractedValue FROM ##TestNew t Any suggestions to get this done? Thank You

tsqlloopsubstringpatinex
Comment

I would personally look into writing a SQL Function using CLR, to make use of the .NET objects for Regular Expressions. Here's a good resource as a starting point for you about regular expressions in T-SQL.
http://msdn.microsoft.com/en-us/magazine/cc163473.aspx

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.

**17** People are following this question.

Copyright 2022 Redgate Software.
Privacy Policy