question

sqlLearner 1 avatar image
sqlLearner 1 asked

Split Value using PATIndex

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thank You I will look into this.
0 Likes 0 ·
Jeff Moden avatar image
Jeff Moden answered
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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.