Milacay Milacay - 17 days ago 9
SQL Question

SQL Server 2008 - Remove the parentheses and anything between

I am having a hard time to figure out how to remove the parentheses and anything inside it, plus the space in front. I tried the combination of CharIndex, Substring, and LEFT, but could not get it work correctly.

Here is the example. Let's say I have a very simple table "ContactName" and a column "DisplayName"

Query would be "Select DisplayName FROM ContactName".

In the ContactName table, the data looks like this:

Joe Blow (joe.blow@yahoo.com)
John Deep (jdeep@gmail.com)
(Tim.King@gmail.com) King, Timothy
Richard Harris
Wang, Justin


What I would like to do is removing anything with the parenthesis including the space in front of it if any. For example, "Joe Blow (joe.blow@yahoo.com)" will become "Joe Blow" with NO space, not "Joe Blow ". Also, "(Tim.King@gmail.com) King, Timothy" will become "King, Timothy"

Example of the result would be like this, and NO trailing spaces after the name.

Joe Blow
John Deep
King, Timothy
Richard Harris
Wang, Justin


Maybe I need some kind of RegEx to accomplish this. Please help.

Thanks in advance,

vkp vkp
Answer

To remove all characters between ( and ) in the string (if they are present), you can use stuff and then a combination of ltrim and rtrim to remove all the trailing and leading spaces.

case when charindex('(',displayname) > 0 and charindex(')',displayname) > 0 
     then rtrim(ltrim(stuff(displayname,charindex('(',displayname),charindex(')',displayname),'')))
else displayname end