Milacay Milacay - 1 year ago 125
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 (
John Deep (
( 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 (" will become "Joe Blow" with NO space, not "Joe Blow ". Also, "( 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 Source

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
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download