Is there an SQL Server function to remove brackets and their contents?

I need to remove all brackets and their contents from multiple records but I can't figure out the best way to do this.

I would like to be able to write something like

SELECT dbo.RemoveBracketedText(ColumnNam开发者_高级运维e) FROM TableName;

And it would convert a record such as 'Hello (World)' to just 'Hello '

Any help is appreciated.

Thanks!

My first approach would probably have been to write a quick c# app to do it or use SSIS and write a package to take care of it, but if your determined to use tsql... it will likely require a combination of a recursive function and some string manipulation.

This is minimally tested but should be close. (i.e. it worked on the sample text you provide in your question).

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

select ltrim(rtrim(
  replace(
    replace(ColumnName, 
      substring(ColumnName, 
                charindex('(',ColumnName), 
                charindex(')',ColumnName) - charindex('(',ColumnName) + 1), 
      ''), 
  '  ',' ')
)) from TableName;