Help on splitting the name field in a query

Question

Post reply

Help on splitting the name field in a query

Mh-397891

SSCertifiable

Points: 6704
More actions
February 27, 2008 at 1:17 pm

#71331

I am trying to extract first name, last name and middle initial from the custname field, any help is greatly appreciated.
Here are few examples of the custname field:
Smith, GRAM, A
Lastname, SEAN G
Sm, Amy
Thanks!

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 1

Have you tried anything on your own??? Have you looked into CHARINDEX or PATINDEX?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

GSquared SSC Guru Points: 260824 More actions · Answer 2

Does it always have a comma between the elements of the name? (Your samples do, but I'm not sure if that's a coincidence.)

Also, does it have entries like "Bob Smith", where the elements are in the right sequence? Or is it always last-first-middle?

How about prefixes and suffixes? Does it have any of those? (Things like "Dr. Joe Jones", or "Sam Smith Jr.")

I'm asking because pattern-matching on names can be incredibly difficult. If they follow a simple pattern of last,first,middle (with the same sequence, and always a comma between elements), then it'll be pretty easy.

Please answer the above and then I'll probably be able to help out.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 3

No it's not consistent, but most of the times it's either:

1. lastname, firstname MI

2. Lastname, Firstname, MI

There are some data issues, just for now let's consider the above combination for splitting the names. Thanks for the help!

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 4

Heh... it's real tough when the OP doesn't have a clue what they want... here's what he posted just before his edit for names...

NYSTROM, GRAHAM, A

CLOSSON, SEAN T

MOORE, AMANDA

In any case, this is super simple using a Tally table, but we do need to know how stable the data is, answer the questions that GSquared asked, and what is the primary key of the table?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 5

Mh (2/27/2008)
No it's not consistent, but most of the times it's either:
1. lastname, firstname MI
2. Lastname, Firstname, MI
There are some data issues, just for now let's consider the above combination for splitting the names. Thanks for the help!

That helps... question would be, what do you want done if they don't have that format?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 6

Also, still need to know the PK of the table for the highspeed Tally table solution...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 7

Then CHARINDEX is your friend. You will need to plit off the last name into its own field FIRST, and then worry about the possibilities of a second comma "later".

Hint: you're looking for the CHARINDEX of the comma, so that you can pull the LEFT side into the LastName column, and then remove the last name from this working column.

You really should give it a whirl.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 8

So far I was able to get the lastname:

left(custname, (charindex(',',CUSTNAME,1)-1)) as [LastName]

How do I get the firstname??

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 9

Let's try another "hint" to see if that works too. You want to grab the SUBSTRING of the combined name starting one character after that very same comma, and as long as the original string...

You're then going to have to figure out if your "first name" also includes the middle name. But that's essentially just repeating what you've just done to pull out the last name.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 10

MH... seriously... just tell me what the PK of the table is...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 11

Actually it's GreatPlains DB, surprisingly theer's no PK, I do'nt work much with GP and pl. do'nt ask me why there's no PK.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 12

Ok... is there anything like a CustID or EmployeeNum or some column that I can relate back to that uniquely identifies a given row? Not quite the same as asking for a PK, but close...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mh-397891 SSCertifiable Points: 6704 More actions · Answer 13

Mh-397891

SSCertifiable

Points: 6704

February 27, 2008 at 3:22 pm

#782910

custnumber field is unique.

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 14

Perfect... I'll be back soon...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)