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

Question

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(ColumnName) FROM TableName;

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

Any help is appreciated.

Thanks!

Answer 1

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