How to extract data only bewteen first bracket from sql server column? Column data looks like,

       Pace - (SPE)-Elkart IN (OEM Sales)
       Pace - (CIS) - San Francisco (CIS)
       Pietila, Robert (4060)
       Pace - (STP) - St Paul Manufacturing

Result expected,

       SPE
       CIS
       4060
       STP
      

CodePudding user response：

Please try the following solution.

SQL

-- DDL and sample data population, start
DECLARE @tbl TABLE (ID INT IDENTITY PRIMARY KEY, tokens VARCHAR(1024));
INSERT INTO @tbl (tokens) VALUES
('Pace - (SPE)-Elkart IN (OEM Sales)'),
('Pace - (CIS) - San Francisco (CIS)'),
('Pietila, Robert (4060)'),
('Pace - (STP) - St Paul Manufacturing'),
('fafa'),
('dada )NO(');
-- DDL and sample data population, end

SELECT *
    , result = IIF(start > 0 and [end] > 0 AND [end] > start, SUBSTRING(tokens, start   1, [end] - start -1), NULL)
FROM @tbl
CROSS APPLY (SELECT start = CHARINDEX('(', tokens), [end] = CHARINDEX(')', tokens)) AS t;

Output