Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
snejsnej
Starting Member
9 Posts |
 Posted - 2015-03-20 : 19:13:50
|
Hello,Using T-SQL r2008.I have a long list of names that looks something like this:Joe.Blow(797),Fred.Rodney(801),Melody.Razza(621),John McEnroe,Hamza El Din,Johnny.Rotten,...I need to remove everything past the first '(', as well as replace the '.' with a space so that all names appear simply as "FirstName LastName". (As you can see, some names contain the offending characters and some do not.)I've tried the following in my SELECT statement: CASE WHEN CHARINDEX('(',[AMA_WEBUSERID])>0 THEN LEFT([AMA_WEBUSERID], CHARINDEX('(', [AMA_WEBUSERID]) - 1) WHEN CHARINDEX('.',[AMA_WEBUSERID])>0 THEN REPLACE([AMA_WEBUSERID],'.',' ') ELSE [AMA_WEBUSERID] END AS [AMA_WEBUSERID] The first WHEN works as expected, so "Joe.Blow(797)" appears as "Joe.Blow"; however the period is still there.I tried a couple of different combinations using AND to try to combine the functions, but all of them gave me syntax errors.Any help appreciated!Jens |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2015-03-20 : 19:27:49
|
| SELECT REPLACE(LEFT([AMA_WEBUSERID]+'(', CHARINDEX('(',[AMA_WEBUSERID]+'(')-1), '.', ' ') |
 |
|
|
snejsnej
Starting Member
9 Posts |
Posted - 2015-03-20 : 20:51:04
|
| Fantastic, thank you. |
|
|
|
|
|
|
|
|
Combine REPLACE and TRIM functions?