Is there a SQL Server function to remove parentheses and their contents?

Question

I need to remove all brackets and their contents from several records, but I cannot figure out how to do this.

I would like to write something like

SELECT dbo.RemoveBracketedText(ColumnName) FROM TableName;

And it converts a record like "Hello (World)" to "Hello"

Any help is appreciated.

Thank!

Answer 1

, , , # SSIS , , tsql... , , .

, . (.. , ).

CREATE FUNCTION RemoveBracketedText (@sourceString varchar(max))
RETURNS varchar(max)
AS
BEGIN

DECLARE @pStart Int
DECLARE @pEnd Int
DECLARE @pTarget varchar(max)
DECLARE @pResult varchar(max)

SET @pStart = CHARINDEX('(', @sourceString)
SET @pEnd = CHARINDEX(')', @sourceString, @pStart) /** start looking from pos of opening bracket */

IF @pEnd > @pStart AND @pEnd > 0  /** basic error avoidance */
BEGIN
  SET @pTarget = SUBSTRING(@sourceString, @pStart, @pEnd - @pStart + 1)
  SET @pResult = Replace(@sourceString, @pTarget, '')

  /** recursion to get rid of more than one set of brackets per string */
  IF CHARINDEX('(', @pResult) > 0 AND CHARINDEX(')', @pResult) > CHARINDEX('(', @pResult)
  BEGIN
    SET @pResult = dbo.RemoveBracketedText(@pResult)  
  END
END
ELSE
BEGIN
  SET @pResult = @sourceString  /** no matching set of brackets found */
END

RETURN @pResult
END