[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Replace Both Brackets All Between?

Posted on 2011-05-09
15
Medium Priority
?
205 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:pointeman
  • 7
  • 4
  • 2
  • +2
15 Comments
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35724115

update table1
set filed1 = REPLACE(Field1, '(CEO)', '')
where Field1 like '%(CEO)%'
0
 

Author Comment

by:pointeman
ID: 35724182
I have many combinations of letters between the two brackets. Would be nice if regular expression possible.
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35724198
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35724304
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 35724345
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
 

Author Comment

by:pointeman
ID: 35724382
I'm understanding the Update, just unsure about removing the ( brackets ) too?
0
 

Author Comment

by:pointeman
ID: 35724423
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
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35728093
Update Table Set Columns = Replace(Columns,'(CEO)','')
0
 

Author Comment

by:pointeman
ID: 35730511
>> Update Table Set Columns = Replace(Columns,'(CEO)','')

Q. Will this Replace statement also remove not only CEO but other various letter combinations?
0
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35731422
>> Update Table Set Columns = Replace(Columns,'(CEO)','')

Removes just (CEO) and other letter combinations are not affected.
0
 

Author Comment

by:pointeman
ID: 35731527
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
 
LVL 23

Expert Comment

by:wdosanjos
ID: 35739477
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
 
LVL 23

Accepted Solution

by:
wdosanjos earned 1000 total points
ID: 35739487
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
 

Author Comment

by:pointeman
ID: 35739921
So this is the only difference?

'%(%)%'
0
 

Author Closing Comment

by:pointeman
ID: 35825440
Sorry it took so long to close this post :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question