Spliting name field into firstname and lastname f

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)

Spliting name field into firstname and lastname f

Author

Topic

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-09-08 : 15:13:26

Hi Guys

I imagine this is a relatively simple task , but how do you split one single fullname field in to two fields i.e first and last for all records in sql server?

i.e
fullnamefield
joe bloggs
pete smith

in to

Firstnamefield
joe
pete

Lastnamefield
bloggs
smith

I know it involves substring and instring but not sure how to do it.

Thank you

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-08 : 15:21:25

search the script forum there i a function called fnsplittext, i think, which you can use

--------------------
keeping it simple...

Zoroaster
Aged Yak Warrior

702 Posts

Posted - 2007-09-09 : 22:34:25

quote:
Originally posted by bendertez

Hi Guys

I imagine this is a relatively simple task , but how do you split one single fullname field in to two fields i.e first and last for all records in sql server?

i.e
fullnamefield
joe bloggs
pete smith

in to

Firstnamefield
joe
pete

Lastnamefield
bloggs
smith

I know it involves substring and instring but not sure how to do it.

Thank you

Here:

SELECT
SUBSTRING(FULLNAMEFIELD, 0, CHARINDEX(' ',FULLNAMEFIELD)) AS FIRSTNAMEFIELD,
SUBSTRING(FULLNAMEFIELD, CHARINDEX(' ', FULLNAMEFIELD)+1,100) AS LASTNAMEFIELD
FROM TABLE

Future guru in the making.

Kristen
Test

22859 Posts

Posted - 2007-09-10 : 02:58:18

What about:

Mary Ann Smith ?

bendertez
Yak Posting Veteran

94 Posts

Posted - 2007-09-10 : 04:36:07

Thanks Zoroaster

That seems to do the trick, however the actual field i'm looking at is a middle name field and some people have a middle name and some people don't.

At the moment the results are being returned as follows

Forename:
Anne Marie

is split as follows:

Firstnamefield:
Anne

MiddlenameField:
Marie

Which is what I want, however if the person does not have a middle name i.e.

Foreneme:
John

it returns:

Firstnamefield:
NULL

Middlenamefield:
John

I want this to be returned the other way around.

i.e.

Firstnamefield:
John

Middlenamefield:
NULL

Any ideas?

Thank you

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-10 : 06:13:22

fnParseString found here
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources