• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 76
  • Last Modified:

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
             ""
0
Bharat Guru
Asked:
Bharat Guru
  • 4
  • 4
  • 2
  • +1
1 Solution
 
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
 
PortletPaulfreelancerCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
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
 
PortletPaulfreelancerCommented:
Do you require more than the examples provided?
0
 
Bharat GuruAuthor Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 4
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now