SQL Server, T-SQL is there a faster way to substring the following string in my question?

Question

I have strings like

OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG)

in my database under my GROUPS Column.

What I want to do is to extract the 2222 from that string. The code I am using is like that.

    SELECT 
        SUBSTRING(GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS,0)+1)+1)+1, 4 ) AS GroupNo

    FROM MY_TABLE

    WHERE

    ISNUMERIC(SUBSTRING(GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS, CHARINDEX('(',GROUPS,0)+1)+1)+1, 4 )) = 1

I need to fasten the above code up, by either changing the substring way I am using , or changing some logic. Can you tell me what things can be improved in my code ?

Answer 1

If you are doing that often I would consider parsing the data on insert/update/delete via a trigger into a separate table (say MY_TABLE_ELEMENTS), and then SELECT from MY_TABLE joined to MY_TABLE_ELEMENTS.

You could, for instance use a split function (splitting on "(", if I understand your code correctly), storing either each entire split element into MY_TABLE_ELEMENTS, or parsing out just the numeric part.

Answer 2

You could implemenet a CLR UDF which implements regex.

Answer 3

If you put the statement in a Derived table, you will be able to make the WHERE clause simple, which should take some time of the execution. This method enables you to use the Alias (GroupNo) in your WHERE method which should already hold the number. I've tested it on my PC with your dummy data and its works.

e.g.

SELECT GroupNo FROM
(
   SELECT SUBSTRING(Groups, CHARINDEX('(',Groups, CHARINDEX('(',Groups, CHARINDEX('(',Groups,0)+1)+1)+1, 4 ) AS GroupNo
   FROM MY_TABLE
) sub
WHERE ISNUMERIC(sub.GroupNo) = 1

As a side note, as you talk about speeding up the query, have you looked into placing an index on the Groups field to see if that improves performance?

Answer 4

A set-based implementation.

This is less performant than your code for single rows, but should scale better for larger result sets, particularly if you replace the CTE generating the dynamic numbers table with a static numbers table.

DECLARE @t TABLE
(groups VARCHAR(250))

INSERT @t
VALUES ('OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(2222)/BTVY(4444)/ACSVTYSAG)')

INSERT @t
VALUES ('OPEN SYSTEMS SUB GR (GM/BTIB(1111)/BTITDBL(3333)/BTVY(4444)/ACSVTYSAG)')

DECLARE @chr_delim CHAR(1)
SET @chr_delim = '('

-- nums_cte generates a dynamic numbers table
-- replace this with your own numbers table if you have one
;WITH nums_cte 
AS 
( 
        SELECT 1 AS n 
        UNION ALL 
        SELECT n+1 FROM nums_cte 
        WHERE n < 250
) 
,splitCTE    
AS
(
        SELECT  SUBSTRING(s,n,CHARINDEX(@chr_delim, s + @chr_delim,n) -n) AS ELEMENT
                ,s
                ,ROW_NUMBER() OVER (PARTITION BY s
                                    ORDER BY n
                                   ) AS rn
        FROM (SELECT groups AS s FROM @t) AS D
        JOIN nums_cte
        ON n <= LEN(s)
        AND SUBSTRING(@chr_delim + s,n,1) = @chr_delim 
)
SELECT LEFT(ELEMENT,4) AS GroupNo
       ,s AS originalString
FROM splitCTE
WHERE rn = 4
AND   ISNUMERIC(LEFT(ELEMENT,4)) = 1
OPTION (MAXRECURSION 0)