Split Field values

Joseph s 1 Reputation point
2021-03-25T13:06:05.383+00:00

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
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
8,551 questions
{count} votes

2 answers

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

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

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


  2. MelissaMa-MSFT 24,121 Reputation points Microsoft Employee
    2021-03-26T01:21:58.9+00:00

    Hi @Joseph s

    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.

    No comments