Search code examples
sql-server-2008charindex

Remove characters between () SQL

I need to remove specific text from a string value in SQL.

I've tried various CHARINDEX and LEN combinations but keep getting it wrong!

I have a name field which, contains names. Some of the fields have text in () added in.

Example : 

Smith (formerly Jones)

I need to remove the whole section inside the brackets. as well as the brackets themselves. Unfortunately sometimes the value can be

Smith (formerly Jones) Reeves

So I can't just remove everything from the ( onwards!

Solution

  • Here are two examples how to accomplish this. You can do this without declaring the @StartIndex and @EndIndex variables, but I have used them for the sake of clarity.

    DECLARE @StartIndex int, @EndIndex int;
DECLARE @Str varchar(100);
SET @Str = 'This is a (sentence to use for a) test';

SELECT @StartIndex = CHARINDEX('(', @Str, 0), @EndIndex = CHARINDEX(')', @Str, 0);

SELECT SUBSTRING(@Str, 0, @StartIndex) + SUBSTRING(@Str, @EndIndex + 1, LEN(@Str) - @EndIndex) AS [Method1],
    LEFT(@Str, @StartIndex - 1) + RIGHT(@Str, LEN(@Str) - @EndIndex) AS [Method2];

    Note that this code does not remove the spaces before or after the parentheses, so you end up with two spaces between "a" and "test" (since that wasn't part of your question).

    Additional error checking should be included before actually using code like this as well, for example if @Str does not contain parentheses it would cause an error.