How to remove braces and values between braces in sql server

 

CREATE FUNCTION [DBO].[FX_REMOVEBRACES] (@TEXT NVARCHAR(MAX))
    RETURNS NVARCHAR(MAX) AS
BEGIN
    DECLARE @TAG_START  INT
    DECLARE @TAG_END    INT
    DECLARE @TAG_LENGTH INT
    SET @TAG_START = CHARINDEX('(', @TEXT)
    SET @TAG_END = CHARINDEX(')', @TEXT, CHARINDEX('(', @TEXT))
    SET @TAG_LENGTH = (@TAG_END - @TAG_START) + 1
    WHILE @TAG_START > 0 AND @TAG_END > 0 AND @TAG_LENGTH > 0
        BEGIN
            SET @TEXT = STUFF(@TEXT,@TAG_START,@TAG_LENGTH, '')
            SET @TAG_START = CHARINDEX('(',@TEXT)
            SET @TAG_END = CHARINDEX(')',@TEXT,CHARINDEX('(', @TEXT))
            SET @TAG_LENGTH = (@TAG_END - @TAG_START) + 1
        END
    RETURN LTRIM(RTRIM(@TEXT))
END


Input :

SELECT dbo.[fx_removebraces]('Metals Ltd(72.39%), Uranium Ltd(20.82%), Exploration NL(6.79%)')


Output :

Energy Metals Ltd, Elevate Uranium Ltd, Southern Cross Exploration NL