Extract string within brackets

Joseph s 1 Reputation point
2021-03-24T14:31:33.26+00:00

Hi all,

I need a little help to extract all the string within and including the square brackets.

Here is an example.

CREATE TABLE TableName  
	(FieldName nvarchar(max))  
  
INSERT INTO dbo.TableName  
  
SELECT 'Test 1546 [JDFH]' UNION  
SELECT 'Testing 562 [DFHI]' UNION  
SELECT 'Test 316 [FF]' UNION  
SELECT 'Testing 475 [KUGMF]' UNION  
SELECT 'Test 5256 [DVDS]' UNION  
SELECT 'Test 2565 [H]'    
  
SELECT *   
FROM dbo.TableName  

What I would like to output is two columns as below.

81170-screenhunter-07-mar-24-1430.gif

No comments
{count} votes

5 answers

Sort by: Most helpful
  1. Tom Phillips 17,511 Reputation points
    2021-03-25T13:26:30.66+00:00

    It is always important when asking a question to include the version of SQL Server you are using.

    Some of your data apparently does not ALWAYS have brackets. That is also important to know.

    This should work:

     CREATE TABLE TableName
         (FieldName nvarchar(max))
    
     INSERT INTO dbo.TableName
    
     SELECT 'Test 1546 [JDFH]' UNION
     SELECT 'Testing 562 [DFHI]' UNION
     SELECT 'Test 316 [FF]' UNION
     SELECT 'Testing 475 [KUGMF]' UNION
     SELECT 'Test 5256 [DVDS]' UNION
     SELECT 'Test 2565 [H]'  UNION
     SELECT 'Test no brackets'  
    
     SELECT 
        *,
        CASE WHEN CHARINDEX('[',FieldName)>0 THEN LEFT(FieldName,CHARINDEX('[',FieldName)-1) ELSE FieldName END,
        CASE WHEN CHARINDEX('[',FieldName)>0 AND CHARINDEX(']',FieldName)>0 THEN SUBSTRING(FieldName,CHARINDEX('[',FieldName)+1,CHARINDEX(']',FieldName) - CHARINDEX('[',FieldName) - 1) ELSE NULL END
    
     FROM dbo.TableName
    

  2. Russel Loski 416 Reputation points
    2021-03-24T14:50:37.073+00:00

    Try this:

     CREATE TABLE TableName
         (FieldName nvarchar(max))
    
     INSERT INTO dbo.TableName
    
     SELECT 'Test 1546 [JDFH]' UNION
     SELECT 'Testing 562 [DFHI]' UNION
     SELECT 'Test 316 [FF]' UNION
     SELECT 'Testing 475 [KUGMF]' UNION
     SELECT 'Test 5256 [DVDS]' UNION
     SELECT 'Test 2565 [H]'  
    
     SELECT * 
     FROM dbo.TableName;
    
    WITH cte as (
    SELECT *, CHARINDEX('[',FieldName) as FirstPOS
    , CHARINDEX(']',FieldName) as SecondPOS
    FROM dbo.TableName
    )
    select FieldName
    , CASE WHEN FirstPOS < SecondPOS
      THEN SUBSTRING(FieldName,FirstPOS + 1, SecondPOS - FirstPOS - 1)
    END AS FieldNameExtracted
    FROM cte;
    
    No comments

  3. Tom Cooper 8,026 Reputation points
    2021-03-24T14:59:31.407+00:00
    ;With cte As
    (Select FieldName, Row_Number() Over(Order By FieldName) As rn
    From TableName)
    Select c.FieldName, 
      Max(Case When s.value Not Like '%]%' Then s.value End),
      Max(Case When s.value Like '%]%' Then Replace(s.value, ']', '') End)
    From cte c
    Cross Apply String_Split(FieldName, '[') s
    Group By c.FieldName;
    

    Tom


  4. Yitzhak Khabinsky 20,016 Reputation points
    2021-03-24T15:43:07.887+00:00

    Hi @Joseph s ,

    Please try the following solution.

    SQL

    -- DDL and sample data population, start  
    DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, col NVARCHAR(MAX));  
    INSERT INTO @tbl (col) VALUES  
    ('Test 1546 [JDFH]'),  
    ('Testing 562 [DFHI]'),  
    ('Test 316 [FF]'),  
    ('Testing 475 [KUGMF]'),  
    ('Test 5256 [DVDS]'),  
    ('Test 2565 [H]');  
    -- DDL and sample data population, end  
      
    SELECT *   
     , LEFT(col, t.posStart - 1) AS col1  
     , SUBSTRING(col, t.posStart + 1, t.posEnd - t.posStart -1) AS col2  
    FROM @tbl  
     CROSS APPLY (SELECT CHARINDEX('[', col), CHARINDEX(']', col)) AS t(posStart, posEnd);  
    
    No comments

  5. EchoLiu-MSFT 14,416 Reputation points
    2021-03-25T01:59:10.84+00:00

    Hi @Joseph s ,

    Please also try:

         SELECT LEFT(FieldName,CHARINDEX('[',FieldName)-1),  
         RIGHT(LEFT(FieldName,LEN(FieldName)-1),LEN(FieldName)-1-CHARINDEX('[',FieldName))  
         FROM dbo.TableName  
    

    Output:

    Test 1546  JDFH  
    Test 2565  H  
    Test 316  FF  
    Test 5256  DVDS  
    Testing 475  KUGMF  
    Testing 562  DFHI  
    

    If you have any question, please feel free to let me know.
    If the response is helpful, please click "Accept Answer" and upvote it.

    Regards
    Echo


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.