split name into forname/surname

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

split name into forname/surname

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-06-02 : 09:12:35

I have two columns, Forname and Surname. Some records have only first name. Most of the names are stored in the Forename column (both the forename AND the surname) and some are stored correctly (forename is in the Forename column and surname is the Surname column). I need to split them so the surname is moved to the right column. Basically I need to (1) check if surname field is empty and if is (2) check if the forename field has a forename followed by space followed by surname, e.g. John Doe. if that condition is also true, (3) split surname and place it in the Surname field. Any help is appreciated. Thanks

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 09:22:10

Do you have records like this?
John M Doe

PBUH

iradev
Starting Member

45 Posts

Posted - 2010-06-02 : 09:35:41

yes, in fact there are records like:
John M Doe
C P A Cooper
Jason van de Roobins
and so on... you get the picture :) The data was migrated from the old system, thats why most names are stored in only one column. Some names are properly stored so a check for an empty surname column is needed beforehand. If its too hard to split names like C P A Cooper i might go over and split those manually (using the front end GUI provided for our database) and just leave all the names with only forename and surname (e.g. Jack Black). What would be the best strategy?

iradev
Starting Member

45 Posts

Posted - 2010-06-02 : 09:40:21

I forgot to mention there is 40,000 names so splitting them all by hand (using the GUI) would be impossible, only a few hundred of those contain more than two `bits`. Also, we only have a forename and surname field. So John M Doe would be split like this:
Forename: John M
Surename: Doe

basically the forname can contain the forename and middlename/initials, but the surname has to contain the surename only

Sachin.Nand

2937 Posts

Posted - 2010-06-02 : 10:03:29

But what about the spaces in the names?
Are they of the same length?

PBUH

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 10:11:26

I use this script from blindman to format name strings. See if it helps.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-02 : 10:27:22

this is what you want

DECLARE @Employee TABLE
(
EmployeeID INT,
ForName VARCHAR(50),
SurName VARCHAR(50)
)
INSERT INTO @Employee
SELECT 1,'John M Doe','' UNION
SELECT 2,'Kevin','Miller' UNION
SELECT 3,'Tom J Cruise',NULL UNION
SELECT 4,'Ralph','Kindall' UNION
SELECT 5,'Bill Inmon',NULL

UPDATE @Employee
SET SurName=LTRIM(REVERSE(SUBSTRING(REVERSE(ForName),1,CHARINDEX(' ',REVERSE(ForName))))),
ForName=REVERSE(SUBSTRING(REVERSE(ForName),CHARINDEX(' ',REVERSE(ForName))+1, LEN(ForName)))

WHERE ISNULL(SurName,'')=''
SELECT * FROM @Employee

vaibhavktiwari83
Aged Yak Warrior

843 Posts

Posted - 2010-06-02 : 10:36:17

This also -


Declare @str as varchar(50)
SET @str = 'C P A Cooper'

SELECT	REPLACE( @str, REVERSE(SUBSTRING(REVERSE(@str), 1, CHARINDEX(' ', REVERSE(@str)) ) ), '') ForName, 
		REVERSE(SUBSTRING(REVERSE(@str), 1, CHARINDEX(' ', REVERSE(@str)) ) ) Surname

Vaibhav T

To walk FAST walk ALONE
To walk FAR walk TOGETHER

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-06-02 : 10:36:33

quote:
Originally posted by GopiMuluka

this is what you want

DECLARE @Employee TABLE
(
EmployeeID INT,
ForName VARCHAR(50),
SurName VARCHAR(50)
)
INSERT INTO @Employee
SELECT 1,'John M Doe','' UNION
SELECT 2,'Kevin','Miller' UNION
SELECT 3,'Tom J Cruise',NULL UNION
SELECT 4,'Ralph','Kindall' UNION
SELECT 5,'Bill Inmon',NULL

UPDATE @Employee
SET SurName=LTRIM(REVERSE(SUBSTRING(REVERSE(ForName),1,CHARINDEX(' ',REVERSE(ForName))))),
ForName=REVERSE(SUBSTRING(REVERSE(ForName),CHARINDEX(' ',REVERSE(ForName))+1, LEN(ForName)))

WHERE ISNULL(SurName,'')=''
SELECT * FROM @Employee

Wont work for cases with a Suffix. Try this for example.

DECLARE @Employee TABLE 
(
EmployeeID INT,
ForName VARCHAR(50),
SurName VARCHAR(50)
)
INSERT INTO @Employee
SELECT 1,'John M Doe Jr','' UNION
SELECT 3,'Tom J Cruise II',NULL UNION
SELECT 4,'Ralph','Kindall' UNION
SELECT 5,'Bill Inmon',NULL

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-02 : 10:44:07

Correct my code doesn't work for suffix..
iradev wasn't mentioned about Suffixes in the ForName field..
If required simply build a table for holding possible suffixes and replace them with empty before executing my Update statement

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-02 : 11:10:19

Please check below version, It works for suffixes also

DECLARE @Employee TABLE
(
EmployeeID INT,
ForName VARCHAR(50),
SurName VARCHAR(50)
)
DECLARE @Suffix TABLE
(
Suffix VARCHAR(50)
)

INSERT INTO @Employee
SELECT 1,'John M Doe Jr','' UNION
SELECT 2,'Kevin Sr','Miller' UNION
SELECT 3,'Tom J Cruise II',NULL UNION
SELECT 4,'Ralph','Kindall' UNION
SELECT 5,'Bill Inmon',NULL

INSERT INTO @Suffix
SELECT 'Jr' UNION
SELECT 'Sr' UNION
SELECT 'II' UNION
SELECT 'III'

SELECT * FROM @Employee

UPDATE @Employee
SET ForName= REPLACE(LTRIM(RTRIM(E.FORNAME)),S.Suffix,'')
FROM @Employee E
CROSS JOIN @Suffix S
WHERE CHARINDEX(S.Suffix,E.FORNAME)>0

SELECT * FROM @Employee

UPDATE @Employee
SET SurName=LTRIM(REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(ForName))),1,CHARINDEX(' ',REVERSE(LTRIM(RTRIM(ForName))))))),
ForName=REVERSE(SUBSTRING(REVERSE(LTRIM(RTRIM(ForName))),CHARINDEX(' ',REVERSE(LTRIM(RTRIM(ForName))))+1, LEN(ForName)))
WHERE ISNULL(SurName,'')=''
SELECT * FROM @Employee

Kristen
Test

22859 Posts

Posted - 2010-06-02 : 11:23:31

Gopi:

Doesn't work for "Jason van de Roobins" (as per O/P example); doesn't put the Suffix back; strips off the suffix if it happens to be the Tail for the name (dunno if that is likely, but becomes more likely as more values added to @Suffix table

Have a read of http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=56499 - folk put a lot of work into that method

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-03 : 03:34:26

I didn't get you on why my code doesn't work for "Jason van de Roobins", I see "Roobins" is placed in SurName column when I initially kept SurName field now.

Also please understand that we are not trying to provide a complete solution for the requestor's problem, we are just providing a direction, though we can try to provide a perfect answer, If he comes up with his complete requirements

Kristen
Test

22859 Posts

Posted - 2010-06-03 : 05:14:06

"I see "Roobins" is placed in SurName "

Indeed, and opinions may vary [;)}, but I think the surname should be "van de Roobins" in that example.

It can always be difficult to gauge ability etc. of folk in forums, but from the style of question the O/P has asked I think he understands the complexity of the task, the nature of his data, and the likely complexity of a solution, and thus I think the complex solution linked to will make a good starting point for him.

I didn't mean to dampen your enthusiasm, but I think we would take a lot of iterations "brewing" a solution here - and that work has already been done in the linked thread.

My experience of these types of solutions is you "split" the name with the rules you have to hand etc. and then months later people start complaining about "this name" and "that name" that didn't split well ... and then its even more of a nightmare to sort out - even if you have the original data to re-do the split you then have to deal with names that have changed, subsequently, in the table and so on.

But I'm a "detail person" so I favour a complex solution

GopiMuluka
Starting Member

12 Posts

Posted - 2010-06-03 : 06:07:59

"But I'm a "detail person" so I favour a complex solution"

Complex solution is not necessarily perfect/complete solution, depends on the requirement if we can attempt the solution in a failrly simpler and SET Based manner that is good in my opinion

Any ways, complex solution provided in the given post is Good

iradev
Starting Member

45 Posts

Posted - 2010-06-09 : 09:59:45

Thank you all for your replies. Much appreciated.
There were no suffixes in my data so GopiMuluka's first post was exactly what I needed.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources