Extract string within brackets

Question

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.

Answer 1

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

Answer 2

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;

Answer 3

;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

Answer 4

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);  

Answer 5

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.