If I have for example the string:
'x(abc),y(def),z(ghi)'
How can I extract out either a comma delimited string:
'abc,def,ghi'
or a table of the strings:
'abc'
'def'
'ghi'
that exist between the two strings '(' and ')'?
They are comma delimited, but each 'element' can be of variable length.
If open to a TVF. Being a TVF, it is easy to use in a CROSS APPLY if your data is in a table.
Example
Declare @S varchar(max) = 'x(abc),y(def),z(ghi)'
Select * from [dbo].[tvf-Str-Extract](@S,'(',')')
Returns
RetSeq RetPos RetVal
1 3 abc
2 10 def
3 17 ghi
The Function if Interested
CREATE FUNCTION [dbo].[tvf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table
As
Return (
with cte1(N) as (Select 1 From (values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cte2(N) as (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
cte3(N) as (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
cte4(N,L) as (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)
Select RetSeq = Row_Number() over (Order By N)
,RetPos = N
,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1)
From ( Select *,RetVal = Substring(@String, N, L) From cte4 ) A
Where charindex(@Delimiter2,RetVal)>1
)
/*
Max Length of String 1MM characters
Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[tvf-Str-Extract] (@String,'[[',']]')
*/
You can use charindex like below:
declare @temp table
(val nvarchar(50))
insert into @temp values ('x(abc)')
insert into @temp values ('y(def)')
insert into @temp values ('z(ghi)')
SELECT
SUBSTRING(val,CHARINDEX('(', val) + 1,CHARINDEX(')', val) - CHARINDEX('(', val) - 1) as Val
from @temp
Ref: SQL Select values inside parenthesis
EDIT: if it is one row including many values inside paranthesis, you need to split it based on comma first. For SQL 2008, I am using the split function specified in this link for years, thanks Andy: T-SQL split string
Create the function below:
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT
WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name
SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList
SELECT @stringToSplit
RETURN
END
And see this: SELECT * FROM dbo.splitstring('x(abc),y(def),z(ghi)')
This way, you can split your data based on comma and use the first charindex strategy I mentioned.
