First name And last Name in SQL

Question

First name And last Name in SQL

Viewing 6 posts - 16 through 20 (of 20 total)

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

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 1

Jeff Moden (12/21/2011)
Dev (12/21/2011)
"Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"
Title FName LName Degree
Agree on Names but Titles & Degrees shouldn't be in source data.
"Shouldn't be" and "aren't" are two totally different worlds. We just saw the op point out that they are in the source data and you have to deal with it. Dealing with it may be getting the provider of the data to turn to and deliver proper data but I trust such providers about as far as I can throw a truck. 😉

ha ha..

I have little bit confidence (may be overconfidence) that Title will not appear here. The reason for my assumption is, table has two columns (FName, LName) which have precise meaning. I wouldn't bet on it if he might have 'FullName' as column. I have seen myself creapy data that I cleansed in ETL (dirty job).

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Yeah... that's why "LastName" had a degree indication in it. 😉

--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)
Intro to Tally Tables and Functions

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 3

Jeff Moden (12/21/2011)
...Even though that works, what are you going to do with first names that begin with a salutation, last names that end with other titles and suffixes, and first names that look like "Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP" ???? 😛
Before you create any code for such a problem, you really need to identify the full extent of the problem.

No kidding. Postnominal processing also has to account for known short surnames like 'Al','An','Au','Bi','Do','Du','Fu','Gu','Ha','He'. If the data really is this dirty, then lookups help distinguish between different elements of the name strings. Titles, postnominals, short surnames - and firstnames too if possible. So;

'Doctor & Mrs.' matches in column [Rawtitle] of Titles lookup and returns [Cleantitle] 'Dr & Mrs'

'Johnathan' matches in Firstnames lookup

'Esq', 'DO', 'MD', 'MVP' each match in Postnominals lookup

Remainder of string 'van Huron' is assumed to be surname - another lookup of surname prefixes ('van', 'von') helps.

At this point, a data sample would go a long way - the dirtier, the better.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 4

Dev (12/21/2011)
"Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"
Title FName LName Degree
Agree on Names but Titles & Degrees shouldn't be in source data.

Try telling that to a direct mailing company!

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 5

ChrisM@home (12/22/2011)
Dev (12/21/2011)
"Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"
Title FName LName Degree
Agree on Names but Titles & Degrees shouldn't be in source data.
Try telling that to a direct mailing company!

It’s my lucky day. Two geniuses crossed my post...

I have seen postal / mailing company database. Their naming & address storage is normalized and they have strict rules for data entry as well. I have worked for a telecom customer where company was spending quite good amount on de-duplicating the address to identify the customer uniquely based on address.

But there is always a scope for goof up. :hehe:

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

Dev (12/22/2011)
ChrisM@home (12/22/2011)
Dev (12/21/2011)
"Doctor & Mrs. Johnathan van Huron Esq. - DO, MD, MVP"
Title FName LName Degree
Agree on Names but Titles & Degrees shouldn't be in source data.
Try telling that to a direct mailing company!
It’s my lucky day. Two geniuses crossed my post...
I have seen postal / mailing company database. Their naming & address storage is normalized and they have strict rules for data entry as well. I have worked for a telecom customer where company was spending quite good amount on de-duplicating the address to identify the customer uniquely based on address.
But there is always a scope for goof up. :hehe:

You know Jeff AND Johnathan van Huron? 😀

The last task at my last gig was writing a name cleansing sproc for a DM company. You can't imagine how rubbish some of that data was. DM companies generally buy up lists and load them to their db, cleansing on route, but the algorithms are sometimes cr@p.

Let's see what the OP comes up with. It's impossible to write a simple solution without seeing the shape of the data.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]