sql - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

sql

Author

Topic

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-21 : 10:12:58

Hi,
Using sql how can I separate the firstname and the surname?
For example:
Mike brown

firstname=mike
surname = brown

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-06-21 : 10:19:18

declare @wholeName varchar(50)
set @wholeName = 'Mike brown'
select left(@wholeName, charindex(' ', @wholeName)) as name, substring(@wholeName, charindex(' ', @wholeName)+1, len(@wholeName))

Go with the flow & have fun! Else fight the flow

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-21 : 10:21:49

can we assume you will have names like:

Thurston Howell III
Mary Jo Fernandez
J. P. Morgan
Cher
Jill St. John
Martin Luther King Jr.
Sir Elton John
Mr. and Mrs. Wile E. Coyote

Be One with the Optimizer
TG

fmardani
Constraint Violating Yak Guru

433 Posts

Posted - 2005-06-21 : 10:23:14

I guess you are right, yes.
Thanks

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-21 : 10:48:20

The problem is that a free-form name field could really be any of 5 fields:

prefix
first
middle
last
suffix

Basically, you'll need to break apart the string based on spaces and come up with rules to handle the majority of the data.

The way I've done this in the past is to create a table like:
origName,part1,part2,part3,part4,part5,part6,prefix,first,middle,last,suffix

break the origName into parts (by spaces)
then create a series of update Statements (rules) to populate prefix,first,middle,Last,suffix columns based on my actual data.

ie: if part1 and part2 are not null and parts 3,4,5,6 are null then first=part1, last=part2

Be One with the Optimizer
TG

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-06-21 : 11:05:41

If the number of spaces donot exceed 4, then consider using PARSENAME

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources