SQL: Pull name from substring .

I am looking to pull out name from a substring, have tried a couple idea's but getting held up on the first name:

Displayed as: Doe, Joseph Moe (Joseph)
Result: Joseph Doe

Any help that can be provided would be much appreciated!
Levi MartinSenior Data AnalystAsked:
Who is Participating?
 
Patrick MatthewsCommented:
SELECT OriginalName, LEFT(OriginalName, CHARINDEX(',', OriginalName) - 1) + ' ' +       SUBSTRING(OriginalName, CHARINDEX('(', OriginalName) + 1, CHARINDEX(')', OriginalName) - CHARINDEX('(', OriginalName) - 1) AS RedoneFROM SomeTable
0
 
Patrick MatthewsCommented:
The moment you hit a first name with >1 words, you are sunk.  Also, can you guarantee that each value always follows:[][]   [] denotes optional
0
 
Levi MartinSenior Data AnalystAuthor Commented:
That's my battle, is I'm trying to capture what's in the parenthesis as the first name. Yes, it will always follow that format:

LastName, FirstName MiddleName (FirstName)
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Jini JoseSenior .Net DeveloperCommented:
kindly try this below code

declare @a varchar(250)
set @a='Doe, Joseph Moe (Joseph)'

select substring(@a,CHARINDEX(',', @a)+1,CHARINDEX(' ', @a,CHARINDEX(',', @a)+2)-CHARINDEX(',', @a)) + ' ' +
substring(@a,0,CHARINDEX(',', @a))
0
 
LCSandman8301Commented:
not to knock it but the way names are going we may have ( in the names soon but check out the code below
select substring(fullname, charindex('(', fullname), charindex(')', fullname) - charindex('(', fullname))
from table1

Open in new window

0
 
Patrick MatthewsCommented:
gmailjini,I think your syntax is adding some extraneous spaces.  Your example has a leading space, and two spaces between the first name and surname.:)Patrick
0
 
vdr1620Commented:
SELECT SUBSTRING(ColumnName,CHARINDEX('(',ColumnName)+1,CHARINDEX(')',ColumnName)-(CHARINDEX('(',ColumnName+1))
+ ' '+
SUBSTRING(ColumnName', 1, CHARINDEX(',',ColumnName)-1)
0
 
Levi MartinSenior Data AnalystAuthor Commented:
First post was great, had an extra space which original poster caught. That's so much for all the follow-up experts!!
0
 
Jini JoseSenior .Net DeveloperCommented:
:)
0
 
Jini JoseSenior .Net DeveloperCommented:
hi matthewspatrick

what happened if the name does not have any brackets ?
0
 
Levi MartinSenior Data AnalystAuthor Commented:
The main IT team here using this name as a P_KEY instead of employee # (don't get me going on that) but they at least always have the first name parenthesis. Seriously, can't thank those poster to this board enough, thanks everyone for the quick follow-up!
0
 
Patrick MatthewsCommented:
>>what happened if the name does not have any brackets ?What happens is, the query fails, but that's why I asked the Asker to guarantee that it would always be there :)Volcrom19: you're most welcome!
0
 
Jini JoseSenior .Net DeveloperCommented:
:)
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.