asked on

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!

Patrick Matthews

The moment you hit a first name with >1 words, you are sunk. Also, can you guarantee that each value always follows:[][] [] denotes optional

Levi Martin

ASKER

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)

SOLUTION

Jini Jose

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Patrick Matthews

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

LCSandman8301

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

Patrick Matthews

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

vdr1620

SELECT SUBSTRING(ColumnName,CHARINDEX('(',ColumnName)+1,CHARINDEX(')',ColumnName)-(CHARINDEX('(',ColumnName+1))
+ ' '+
SUBSTRING(ColumnName', 1, CHARINDEX(',',ColumnName)-1)

Levi Martin

ASKER

First post was great, had an extra space which original poster caught. That's so much for all the follow-up experts!!

Jini Jose

hi matthewspatrick

what happened if the name does not have any brackets ?

Levi Martin

ASKER

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!

Patrick Matthews

>>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!

Jini Jose