Ho w to write extract function between brackets for SQL 2012

How to write mysubstring function to extract all value between brackets for example

select  mysubstring("This is a test [AB, BD, FE]  test2 [GF, JE,FT] test2 ")
    The output should be
          AB, BD, FE, GF, JE,FT

select  mysubstring("This is a test [AB, BD, FE]  test2 ")
    The output should be
          AB, BD, FE

select  mysubstring("This is a test")
The output should be
             ""
Bharat GuruAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
I provided the core of this code in your other question.  Converting it to a function is trivial.

DECLARE @Test VARCHAR(MAX) = 'This is a test [AB, BC, BD] test [EF, BD]'

DECLARE @List TABLE (Item VARCHAR(MAX));

WITH cte AS
(
	SELECT @Test AS String,
		CHARINDEX('[', @Test, 1) AS StartIndex,
		CHARINDEX(']', @Test, 1) AS EndIndex,
		SUBSTRING(@Test, CHARINDEX('[', @Test, 1) + 1, CHARINDEX(']', @Test, 1) - CHARINDEX('[', @Test, 1) - 1) AS List,
		SUBSTRING(@Test, CHARINDEX(']', @Test, 1) + 1, LEN(@Test)) AS Remaining
	UNION ALL
	SELECT Remaining AS String,
		CHARINDEX('[', Remaining, 1) AS StartIndex,
		CHARINDEX(']', Remaining, 1) AS EndIndex,
		SUBSTRING(Remaining, CHARINDEX('[', Remaining, 1) + 1, CHARINDEX(']', Remaining, 1) - CHARINDEX('[', Remaining, 1) - 1) AS List,
		SUBSTRING(Remaining, CHARINDEX(']', Remaining, 1) + 1, LEN(Remaining)) AS Remaining
	FROM cte
	WHERE CHARINDEX('[', Remaining, 1) > 0
)
SELECT RTRIM(LTRIM(S.[value])) AS item
FROM cte
CROSS APPLY STRING_SPLIT(cte.List, ',') AS S

Open in new window

0
PortletPaulEE Topic AdvisorCommented:
Using 2 recently included system functions:
 STRING_SPLIT() (mssql 2016) and
STRING_AGG() (mssql 2017) you can achieve this as follows:

CREATE TABLE myTable    ([mysubstring] varchar(52));
INSERT INTO myTable     ([mysubstring])
VALUES
    ('This is a test [AB, BD, FE]  test2 [GF, JE,FT] test2'),
    ('This is a test [AB, BD, FE]  test2'),    ('This is a test');

select
  mysubstring
, string_agg(case when CHARINDEX(']', value, 1) > 0 
             then left(value, CHARINDEX(']', value, 1)-1 ) else NULL end, ', ') y
from (
    select mysubstring, value
    from mytable
    cross apply string_split(mysubstring,'[')
    ) d
group by 
  mysubstring
;

| mysubstring                                          | y                     |
|------------------------------------------------------|-----------------------|
| This is a test                                       | (null)                |
| This is a test [AB, BD, FE]  test2                   | AB, BD, FE            |
| This is a test [AB, BD, FE]  test2 [GF, JE,FT] test2 | AB, BD, FE, GF, JE,FT |

Open in new window

see it working at: http://sqlfiddle.com/#!18/d9c87/2
0
Bharat GuruAuthor Commented:
I'm using ms sql 2012
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Bharat GuruAuthor Commented:
how can I do exactly same as above for sql 2012 server
0
Brian CroweDatabase AdministratorCommented:
Replace any reference to STRING_SPLIT with your own split function.

https://sqlperformance.com/2012/07/t-sql-queries/split-strings
0
Bharat GuruAuthor Commented:
string_agg is not a recognized built-in function name in sql server 2012
0
PortletPaulEE Topic AdvisorCommented:
I identifed 2 functions and when they come into the product both become available after 2012 was released

ForSQL Server 2012 you need to
 - use your own function in place of string_split (Brian has given you a url for that)
 - use FOR XML PATH instead of string_agg
0
patel_komalCommented:
Please show me the FOR XML PATH example
0
PortletPaulEE Topic AdvisorCommented:
I have created a user defined function for splitting the string into parts based on dbo.SplitStrings_Moden
 found at https://sqlperformance.com/2012/07/t-sql-queries/split-strings which I named simply "split_string"
CREATE TABLE myTable
    ([mysubstring] varchar(52))
;
   
INSERT INTO myTable 
    ([mysubstring])
VALUES
    ('This is a test [AB, BD, FE]  test2 [GF, JE,FT] test2'),
    ('This is a test [AB, BD, FE]  test2'),
    ('This is a test')
;

CREATE FUNCTION dbo.Split_String /* dbo.SplitStrings_Moden https://sqlperformance.com/2012/07/t-sql-queries/split-strings */
(
   @List NVARCHAR(MAX),
   @Delimiter NVARCHAR(255)
)
RETURNS TABLE
WITH SCHEMABINDING AS
RETURN
  WITH 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),
       E2(N)        AS (SELECT 1 FROM E1 a, E1 b),
       E4(N)        AS (SELECT 1 FROM E2 a, E2 b),
       E42(N)       AS (SELECT 1 FROM E4 a, E2 b),
       cteTally(N)  AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) 
                         ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42),
       cteStart(N1) AS (SELECT t.N+1 FROM cteTally t
                         WHERE (SUBSTRING(@List,t.N,1) = @Delimiter OR t.N = 0))
SELECT 
    Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000))
FROM cteStart s
;

Open in new window

as SQL 2012 does not have string_agg() avalable a more arcane method ("for xml path") may be used (originally designed for manipulation of XML) but can be used for string aggregations as needed here.
WITH
      cte1 AS (
                  SELECT
                        mysubstring
                      , item
                      , case when CHARINDEX(']', item, 1) > 0 then left(item, CHARINDEX(']', item, 1)-1 ) else NULL end y
                  FROM mytable
                  CROSS APPLY dbo.split_string(mysubstring, '[')
            ),
      cte2 AS (
                  SELECT
                        mysubstring
                      , oa.items
                  FROM cte1
                  OUTER APPLY (
                        SELECT
                              STUFF(
                                   (SELECT
                                          ',' + y
                                    FROM cte1 AS x
                                    WHERE cte1.mysubstring = x.mysubstring
                                    ORDER BY y
                                    FOR xml PATH ('')
                                    )
                              , 1, 1, '') d
                  ) oa (items)
            )
SELECT DISTINCT
       mysubstring
     , items
FROM cte2

Open in new window

with the result from the sample of:
| mysubstring                                          | items                 |
|------------------------------------------------------|-----------------------|
| This is a test                                       | (null)                |
| This is a test [AB, BD, FE]  test2                   | AB, BD, FE            |
| This is a test [AB, BD, FE]  test2 [GF, JE,FT] test2 | AB, BD, FE, GF, JE,FT |

Open in new window

see http://sqlfiddle.com/#!18/60f19/1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Do you require more than the examples provided?
0
Bharat GuruAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.