question

Josephs-7581 avatar image
0 Votes"
Josephs-7581 asked EchoLiu-msft edited

Extract string within brackets

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


sql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RusselLoski-0634 avatar image
0 Votes"
RusselLoski-0634 answered

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;
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

TomCooper-6989 avatar image
0 Votes"
TomCooper-6989 answered EchoLiu-msft edited
 ;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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I'm getting the following error message for String_Split

Msg 208, Level 16, State 1, Line 1
Invalid object name 'String_Split'.

0 Votes 0 ·

The reason for this error is that your sql server version should be before 2016.String_Split applies to SQL Server 2016 and later.

0 Votes 0 ·
YitzhakKhabinsky-0887 avatar image
0 Votes"
YitzhakKhabinsky-0887 answered YitzhakKhabinsky-0887 edited

Hi @Josephs-7581,

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);
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @Josephs-7581,

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.



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

When I put the results into a table I get the following error message

Msg 537, Level 16, State 3, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

0 Votes 0 ·

The data you provide should be incomplete. My method works for the data you provide.Please provide more complete test data, including various types of data.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered Josephs-7581 commented

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
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thanks Tom

0 Votes 0 ·