Remove text within parenthesis in SQL

Question

I'm trying to remove parenthesis and text within it. Eg: Column1 has data and cleaned data should look like column2

Column1       Column2
HF(abcd)      HF
BP(234)       BP
ATRS (2354)   ATRS
AB(PS) SD(12) AB SD

I'm trying to use the below regex just to view the cleaned data, But I'm doing it wrong

s/([^)]*)//

select Column1 from table where Column1 like '%s/$[^)]*$//%'

What is the best possible solution for this

user_1144035 · Answer

THIS ANSWERED THE ORIGINAL VERSION OF THE QUESTION.

What regular expression? You just want everything before the (:

select left(column1, charindex('(', column1) - 1)

If you might not have the opening paren, you can do:

select left(column1, charindex('(', column1 + '(') - 1)

user_5529445 · Answer

Try Common Table Expression (CTE) with UNION as below :

Table Data :

;WITH cte
AS (
    SELECT NAME AS [Column1]
        ,CASE 
            WHEN charindex('(', NAME) > 0
                AND charindex(')', NAME) > charindex('(', NAME)
                THEN stuff(NAME, charindex('(', NAME), charindex(')', NAME) - charindex('(', NAME) + 1, '')
            ELSE NAME
            END AS [Column2]
        ,0 AS [Level] FROM #TM

    UNION ALL

    SELECT [Column1]
        ,stuff([Column2], charindex('(', [Column2]), charindex(')', [Column2]) - charindex('(', [Column2]) + 1, '') AS [Column2]
        ,[Level] + 1 AS [Level]
    FROM cte
    WHERE charindex('(',[Column2]) > 0
    )
    ,cte1
AS (
    SELECT *
        ,row_number() OVER (
            PARTITION BY [Column1] ORDER BY [Level] DESC
            ) AS Rn
    FROM cte
    )
SELECT Column1, Column2
FROM cte1 where Rn = 1

Result :

Remove text within parenthesis in SQL

2 Answers