- Posted by António Lourenço
- On May 9, 2019
- 0 Comments
- sql server, T-SQL
Se preferirem ver este post em português, podem visitar o blog do autor onde foi colocado originalmente.
Some days ago I needed to extract configuration values from a string column on a table with this style
'( [B2] = 1 ) and ( [B32] = 1 ) and ( [A38] = 1 )'
In this specific case I needed to find the “A numbers”, 38 in the example.
I am lazy and I know that not so many time in the future I would need to know the “B numbers” so I needed to create a function that addressed all problems and make me never again think on this issue.
I also like DRY a lot, so I searched on the usual places for solutions that I could adapt to my specific case.
Found many interesting ones, even a nice one recurring to recursive CTE’s (pun intended) but none of them adapted well to my needs without “overkilling” it.
The difficulties came because I don’t need to know to find the positions of the pattern or to grab the pattern, but simply find the start pattern ‘[A’ and grab till the end pattern ‘]’.
I share my SQL just for the ones that could have a similar need, it’s a suitable base that you could adapt.
To get to the final solution, I started using PATINDEX because, in my first analysis, I was looking for a “pattern”, but CHARINDEX was more suitable. First, I can search for ‘[A’, ‘[B’ and ‘]’ directly without any side effects of using chars that are used by LIKE as REGEX, and second, most important, I can just use the third parameter to search from certain position (unavailable on PATINDEX). My code became simpler and more readable.
I know that my string will be short, maybe with 6 parameters max, and that will be called at most one time a day, so I didn’t went deep on performance issues.
Here is the code with a similar function to the final implementation.
USE [TEST_DATABASE] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE function [dbo].[ExtractPatternFromString] ( @SomeString varchar(max) , @SearchType char(1) ) returns @Dependencies table (Dependency int) with schemabinding as begin declare @Separator varchar(10) = ']' declare @Pattern varchar(10) declare @index0 int = 0 declare @index1 int = 0 declare @index2 int = 1 if @SearchType = 'A' begin set @Pattern = '[A' end else begin set @Pattern = '[B' end while ( @index2 > 0) begin select @index1 = charindex(@Pattern, @SomeString, @index0) - -get first [A if @index1 = 0 break; - -if it not present, exit immediatly select @index2 = charindex(@Separador, @SomeString, @index1 ) set @index0 = @index2 insert into @Dependencies select substring(@SomeString, @index1 +2, @index2 -@index1 -2) end return end GO <br>
Usage is simple,
select Dependency from ExtractPatternFromString ( '( [B2] = 1 ) and ( [B32] = 1 ) and ( [A38] = 1 )', 'A')
Hope that it might help someone with the same needs.