question

Josephs-7581 avatar image
0 Votes"
Josephs-7581 asked MelissaMa-msft commented

Split Field values

Hi all, I need a little help to extract all the string within and including the brackets and output them into separate columns and into a final table please. Here is an example of some of the fields in the source table.





  CREATE TABLE TableName 

      (FieldName nvarchar(max)) 

         

  INSERT INTO dbo.TableName 

         

  SELECT 'Urology Consultants OSSD (GSD)' UNION 

  SELECT 'Speech and Lang Speech Recdd (BSDF)' UNION 

  SELECT 'Psychology Con Speech Recdd (FD)' UNION 

  SELECT 'Immunology/Allergy Consultant (HRB)' UNION 

  SELECT 'Gastro Endoscopy Nurseses (HRB)' UNION 

  SELECT 'Histology-Cellular Pathology Speech Reced (WFSP)'   

         

  SELECT *  

  FROM dbo.TableName 





I would like the output to be like the below.



thanks,



81536-screenhunter-09-mar-25-1304.gif





sql-server-generalsql-server-transact-sql
· 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.

@Josephs-7581,

Please delete this post.
It is almost identical to your previous post.
Just square brackets became parenthesis.

Many people already provided you an answer there.

0 Votes 0 ·

Hi @Josephs-7581

Could you please validate all the answers so far and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards
Melissa

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

How is this different from your other question? The solution is the same.

https://docs.microsoft.com/en-us/answers/questions/329869/extract-string-within-brackets.html

· 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.

solution is not working for me.

0 Votes 0 ·

thanks tom, just seen your new message

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered MelissaMa-msft edited

Hi @Josephs-7581

Welcome to Microsoft Q&A!

Please refer your previous post mentioned by other experts which is similar with this post. You could only replace '[]' with '()' in the queries.

Please also refer below:

 SELECT *,LEFT(FieldName,CHARINDEX('(',FieldName)-2) COL1,
 SUBSTRING(FieldName,CHARINDEX('(',FieldName)+1,LEN(FieldName)-CHARINDEX('(',FieldName)-1) COL2
 FROM dbo.TableName 

If '(' is missing in some rows, you could refer below:

 SELECT *,CASE WHEN CHARINDEX('(',FieldName)<>0 THEN LEFT(FieldName,CHARINDEX('(',FieldName)-1) ELSE FieldName END COL1,
 CASE WHEN CHARINDEX('(',FieldName)<>0 THEN SUBSTRING(FieldName,CHARINDEX('(',FieldName)+1,CHARINDEX(')',FieldName)-CHARINDEX('(',FieldName)-1) ELSE '' END COL2
 FROM dbo.TableName 

Output:
81590-output.png

If above is still not working, please provide more sample data and expected output.

Thanks for your undertstanding.

Best regards
Melissa


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.


output.png (8.2 KiB)
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.