In SQL Splitting the name from a col

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

Transact-SQL (2000)

In SQL Splitting the name from a col

Author

Topic

sqlserverdeveloper
Posting Yak Master

243 Posts

Posted - 2008-02-27 : 15:22:45

I am trying to extract first name, last name from the custname field, any help is greatly appreciated.
Here are few examples of the custname field:
NYSTROM, GRAHAM, A
CLOSSON, SEAN T
MOORE, AMANDA
Thanks!

jhocutt
Constraint Violating Yak Guru

385 Posts

Posted - 2008-02-27 : 15:34:11

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

"God does not play dice" -- Albert Einstein
"Not only does God play dice, but he sometimes throws them where they cannot be seen."
-- Stephen Hawking

mayoorsubbu
Yak Posting Veteran

95 Posts

Posted - 2008-03-02 : 02:49:33

The function created by blindman is terrific. The SQL statement below also does the job if the name are delimited by a ',' and
contains a fname + midname nd lastname as in case of the first record (NYSTROM, GRAHAM, A).
SELECT CUSTNAME, LEFT(CUSTNAME, CHARINDEX(',', CUSTNAME)-1) AS FNAME,
SUBSTRING(CUSTNAME,
CHARINDEX(',', CUSTNAME) + 2, CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME)+1) -2
- CHARINDEX(',', CUSTNAME)) AS MIDNAME,
SUBSTRING(CUSTNAME,
CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME)+1)+2, LEN(CUSTNAME) -
CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME))) AS LNAME FROM XX

May be sqlserverdeveloper can develop further on it. Let me know too.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2008-03-02 : 09:52:40

quote:
Originally posted by mayoorsubbu

The function created by blindman is terrific. The SQL statement below also does the job if the name are delimited by a ',' and
contains a fname + midname nd lastname as in case of the first record (NYSTROM, GRAHAM, A).
SELECT CUSTNAME, LEFT(CUSTNAME, CHARINDEX(',', CUSTNAME)-1) AS FNAME,
SUBSTRING(CUSTNAME,
CHARINDEX(',', CUSTNAME) + 2, CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME)+1) -2
- CHARINDEX(',', CUSTNAME)) AS MIDNAME,
SUBSTRING(CUSTNAME,
CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME)+1)+2, LEN(CUSTNAME) -
CHARINDEX(',', CUSTNAME, CHARINDEX(',', CUSTNAME))) AS LNAME FROM XX

May be sqlserverdeveloper can develop further on it. Let me know too.

But the OP had an example where the middle initial was not delimited with a comma.

Poor planning on your part does not constitute an emergency on my part.

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2008-03-04 : 22:19:18

Solved here...

[url]http://www.sqlservercentral.com/Forums/Topic461237-8-1.aspx#bm461364[/url]

--Jeff Moden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources