Replace Both Brackets All Between?

I have a 'FirstName' column filled during an Excel import which contains many names having unwanted brackets and letters like: (CEO), (AIG), etc...

How do I make this: Alfred(CEO)
look like this: Alfred
pointemanAsked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
Sorry, a small change to the where clause:

update @YourTable
      set FirstName = RTRIM(LTRIM(replace(left(FirstName, CHARINDEX('(',FirstName)-1) + right(FirstName, Len(FirstName) - CHARINDEX(')',FirstName)), '  ', ' ')))
where FirstName like '%(%)%'
0
 
Ephraim WangoyaCommented:

update table1
set filed1 = REPLACE(Field1, '(CEO)', '')
where Field1 like '%(CEO)%'
0
 
pointemanAuthor Commented:
I have many combinations of letters between the two brackets. Would be nice if regular expression possible.
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
wdosanjosCommented:
Alternatively (it gets rid of all characters after the first open parenthesis character):

update YourTable
      set FirstName = RTRIM(LTRIM(left(FirstName, CHARINDEX('(',FirstName)-1)))
where FirstName like '%(%'
0
 
Patrick MatthewsCommented:
To make it a little more flexible, this truncates at the first character not likely to appear in a name:

UPDATE SomeTable
SET FirstName = LTRIM(RTRIM(LEFT(FirstName, PATINDEX('[^A-Z -]', FirstName) - 1)))
WHERE PATINDEX('[^A-Z -]', FirstName) > 0

Open in new window


That allows letters, spaces, and dashes, but nothing else.
0
 
Patrick MatthewsCommented:
Sorry, should have been:

UPDATE SomeTable
SET FirstName = LTRIM(RTRIM(LEFT(FirstName, PATINDEX('%[^A-Z -]%', FirstName) - 1)))
WHERE PATINDEX('%[^A-Z -]%', FirstName) > 0

Open in new window

0
 
pointemanAuthor Commented:
I'm understanding the Update, just unsure about removing the ( brackets ) too?
0
 
pointemanAuthor Commented:
I used this, works great:

update YourTable
      set FirstName = RTRIM(LTRIM(left(FirstName, CHARINDEX('(',FirstName)-1)))
where FirstName like '%(%'

Just not sure about PATINDEX removing brackets too.
0
 
Alpesh PatelAssistant ConsultantCommented:
Update Table Set Columns = Replace(Columns,'(CEO)','')
0
 
pointemanAuthor Commented:
>> Update Table Set Columns = Replace(Columns,'(CEO)','')

Q. Will this Replace statement also remove not only CEO but other various letter combinations?
0
 
wdosanjosCommented:
>> Update Table Set Columns = Replace(Columns,'(CEO)','')

Removes just (CEO) and other letter combinations are not affected.
0
 
pointemanAuthor Commented:
I would like to awards points for this example because it worked for my case:

update YourTable
      set FirstName = RTRIM(LTRIM(left(FirstName, CHARINDEX('(',FirstName)-1)))
where FirstName like '%(%'

However, for future referrence, I would still like to know how to remove both brackets and letters 'between'...
0
 
wdosanjosCommented:
The following SQL can do the trick:

update YourTable
      set FirstName = RTRIM(LTRIM(replace(left(FirstName, CHARINDEX('(',FirstName)-1) + right(FirstName, Len(FirstName) - CHARINDEX(')',FirstName)), '  ', ' ')))
where FirstName like '%(%'
0
 
pointemanAuthor Commented:
So this is the only difference?

'%(%)%'
0
 
pointemanAuthor Commented:
Sorry it took so long to close this post :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.