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)
 Parsing a field with T-SQL

Author  Topic 

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 14:02:40
I have a routine in Excel that will parse out a column with a person’s full name. I need to break the column up into three separate columns with T-SQL. Here are the scripts that I am using:

This section describes several worksheet functions you can use to split full names into the first and last name components.

To return the last name of the full name in A2, use the following formula.

=LEFT(A1,IF(ISERROR(FIND(" ",A1,1)),LEN(A1),FIND(" ",A1,1)-1))


To return the first name of the full name in A2, use the following formula.

=TRIM(IF(ISERROR(FIND(" ",A1,1)),A1,MID(A1,FIND(" ",A1,1)+1,
IF(ISERROR(FIND(" ",A1,FIND(" ",A1,1)+2)),LEN(A1),
FIND(" ",A1,FIND(" ",A1,1)+2))-FIND(" ",A1,1))))


To return the middle name of the full name in A2, use the following formula.

=TRIM(RIGHT(A1,LEN(A1)-IF(ISERROR(FIND(" ",A1,
FIND(" ",A1,FIND(" ",A1,1)+2))),LEN(A1),
FIND(" ",A1,FIND(" ",A1,FIND(" ",A1,1)+2))-1)))



Any suggestions would be appreciated.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:04:43
Can you show us an example of the data and how you want it to look?

Tara
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 14:10:35
Raw Data

Col001

AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P

SELECT SUBSTRING(Col001,1,CHARINDEX(' ',Col001)) AS LName,
SUBSTRING(Col001,CHARINDEX(' ',Col001)+1, CHARINDEX(' ',Col001,CHARINDEX(' ',Col001)+1)-CHARINDEX(' ',Col001)+20) AS FName,
REVERSE(SUBSTRING(REVERSE(Col001),1,CHARINDEX('',REVERSE(Col001)))) AS MName
FROM table

Gives me this:

LName FName MName

AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P

The AS MName is what is killing me. How can I get the MName field populated? I have looked at the Tame Those Strings articles but I am still lost.

Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 14:13:14
The gives me did not post correctly. I am getting LName correctly but FName and MName are still in the same column. I do not have anything in the MName column. Help!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:33:57
PARSENAME is easiest for this:



SET NOCOUNT ON

DECLARE @Table table (Col001 varchar(100) NOT NULL)

INSERT INTO @Table VALUES('AABY TAMARA J')
INSERT INTO @Table VALUES('AADNESEN MARJORIE')
INSERT INTO @Table VALUES('AARANT MAUREEN R')
INSERT INTO @Table VALUES('AARON MICHELLEE')
INSERT INTO @Table VALUES('AARON PATRICIA J')
INSERT INTO @Table VALUES('ABBA DAVID W')
INSERT INTO @Table VALUES('ABBATE ANTHONY P')

SELECT
LName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 2)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 3)
END,
FName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 1)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 2)
END,
MName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN ''
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 1)
END
FROM @Table




Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:40:07
Here's some information about PARSENAME:

http://www.sqlteam.com/item.asp?ItemID=15044

Tara
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 14:46:04
Tara, Thanks! You got me pointed in the right direction but I am still not getting the proper results. Here is somemore raw data:

RAW DATA
Col001

A S CONSTRUCTION INC
A S CONSTRUCTION INC
A S CONSTRUCTION INC
AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P
ABBOTT CHARLES C
ABBOTT FRED
ABBOTT JAMES P
ABBOTT JULIA L
ABBOTT KEITH E
ABBOTT MIKE E
ABBOTT PAUL R LEE H
ABBOTT REALTY
ABBOTT REALTY
ABBOTT ROBERT L
ABBOTT ROBERT M
ABBOTT SAKUE
ABBOTT STEVEN F
ABBOTT TED W
ABBOTT WILLIAM E
ABDON RYANN P
ABEL DIANE M
ABEL JUDITH D
ABERCROMBIE WILLIAM
ABERNATHY ALLISON
ABERNETHY JAMES T
ABINGTON S R
ABLES RALPH R
ABNER KERRY L
ABNEY PAMELA J
ABRAHAM WARREN MARGARET
ABRAHAMSEN DIANE SUE
ABRAHAMSEN ELIZABETH
ABRAHAMSEN MELISSA D
ABRAHAMSEN MICHELLE
ABRAHAMSON MATT
ABRAM ANNETTE
ABRAMS ABRAXAS D
ABRAMS BARBARA
ABRAMS ROSE M
ABSHIRE ERIC A
ABUAN MARIA P
ACCARDI AMY-JO
ACEVEDO ANGEL R
ACEVES MARIA D NUNEZ
ACHESON SUSAN A
ACHTZEHN FREDERICK W
ACKER BARBARA
ACKLIN NICHOLE
ACOL JR JAMES N
ACOLY JACEY N
ACOSTA ROBERTO
ACREE BRENT
ACREE DISARAE C
ACREE MINDI L
ACRI ALAINA T
ACTON MARCELLA D
ADAIR PHILLIP E
ADAIR TERRY L
ADAM VERONICA W
ADAMS ANN
ADAMS BESSIE M
ADAMS BEVERLY S
ADAMS BEVERLY S
ADAMS BILLIE R SR
ADAMS CHAS L
ADAMS DEBRA A
ADAMS DONALD
ADAMS DONNA JEAN
ADAMS EARL LEE
ADAMS GALE L
ADAMS GRANT
ADAMS GREGG D
ADAMS HARRY H
ADAMS J DOUGLAS
ADAMS JAMES D
ADAMS JAMES E
ADAMS JASON R
ADAMS JERRY
ADAMS JOE MARYE
ADAMS JOHN H
ADAMS JOHN P
ADAMS JOSEPH E
ADAMS LANGSTON
ADAMS LESTER M
ADAMS LINLEY
ADAMS LISA A
ADAMS LYNNE
ADAMS MARK J
ADAMS MICHAEL J
ADAMS MICHAEL R
ADAMS NICHOLAS W
ADAMS PASCALE S
ADAMS PATRICIA W
ADAMS R E


After I run your script, I get these results:

LName FName MName
S CONSTRUCTION INC
S CONSTRUCTION INC
S CONSTRUCTION INC
AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P
ABBOTT CHARLES C
ABBOTT FRED
ABBOTT JAMES P
ABBOTT JULIA L
ABBOTT KEITH E
ABBOTT MIKE E
NULL NULL
ABBOTT REALTY
ABBOTT REALTY
ABBOTT ROBERT L
ABBOTT ROBERT M
ABBOTT SAKUE
ABBOTT STEVEN F
ABBOTT TED W
ABBOTT WILLIAM E
ABDON RYANN P
ABEL DIANE M
ABEL JUDITH D
ABERCROMBIE WILLIAM
ABERNATHY ALLISON
ABERNETHY JAMES T
ABINGTON S R
ABLES RALPH R
ABNER KERRY L
ABNEY PAMELA J
ABRAHAM WARREN MARGARET
ABRAHAMSEN DIANE SUE
ABRAHAMSEN ELIZABETH
ABRAHAMSEN MELISSA D
ABRAHAMSEN MICHELLE
ABRAHAMSON MATT
ABRAM ANNETTE
ABRAMS ABRAXAS D
ABRAMS BARBARA
ABRAMS ROSE M
ABSHIRE ERIC A
ABUAN MARIA P
ACCARDI AMY-JO
ACEVEDO ANGEL R
MARIA D NUNEZ
ACHESON SUSAN A
ACHTZEHN FREDERICK W
ACKER BARBARA
ACKLIN NICHOLE
JR JAMES N
ACOLY JACEY N
ACOSTA ROBERTO
ACREE BRENT
ACREE DISARAE C
ACREE MINDI L
ACRI ALAINA T
ACTON MARCELLA D
ADAIR PHILLIP E
ADAIR TERRY L
ADAM VERONICA W
ADAMS ANN
ADAMS BESSIE M
ADAMS BEVERLY S
ADAMS BEVERLY S
BILLIE R SR
ADAMS CHAS L
ADAMS DEBRA A
ADAMS DONALD
ADAMS DONNA JEAN
ADAMS EARL LEE
ADAMS GALE L
ADAMS GRANT
ADAMS GREGG D
ADAMS HARRY H
ADAMS J DOUGLAS
ADAMS JAMES D
ADAMS JAMES E
ADAMS JASON R
ADAMS JERRY
ADAMS JOE MARYE
ADAMS JOHN H
ADAMS JOHN P
ADAMS JOSEPH E
ADAMS LANGSTON
ADAMS LESTER M
ADAMS LINLEY
ADAMS LISA A
ADAMS LYNNE
ADAMS MARK J
ADAMS MICHAEL J
ADAMS MICHAEL R
ADAMS NICHOLAS W
ADAMS PASCALE S
ADAMS PATRICIA W
ADAMS R E
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:47:57
Some of your data has 4 parts, which is the maxiumum allowed by PARSENAME. So you'll have to extend the query to the 4th part of PARSENAME. As you can see from my query, I've only done 1,2,3.

Tara
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 14:50:06
The post does not display properly. It gets the majority of the elements in the proper place but I am getting NULLs where there is data. On the first record it skipped the A in "A S CONSTRUCTION INC"
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:50:07
If you specifically tell me which rows have problems, then I'll be able to help you. I'm not going to add all of your data to my table, so please point me in the right direction with the problematic rows or have a shot at it yourself as it's almost there anyway. The problem, I think, is that you have some rows that have 4 parts. So you'll need to extend the query to capture that. BTW, PARSENAME has a limitation of 4 parts, so if you've got any data that has more than 4 parts, then you can't use PARSENAME for those.

Tara
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 14:51:32
Put code tags around data and queries. Code tags are [ c o d e] and [ / c o d e] (without the space).

Tara
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 14:53:32
>> I need to break the column up into three separate columns with T-SQL
for A S CONSTRUCTION INC what do you want in the three columns?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 15:07:11
I guess since I am limited to 4 elements. We could just make four columns called Col001, Col002, Col003 and Col004 and split it across. Here is the RAW DATA again with the tags:

Col001 (RAW)

A S CONSTRUCTION INC
A S CONSTRUCTION INC
A S CONSTRUCTION INC
AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P
ABBOTT CHARLES C
ABBOTT FRED
ABBOTT JAMES P
ABBOTT JULIA L
ABBOTT KEITH E
ABBOTT MIKE E
ABBOTT PAUL R LEE H
ABBOTT REALTY
ABBOTT REALTY
ABBOTT ROBERT L
ABBOTT ROBERT M
ABBOTT SAKUE
ABBOTT STEVEN F
ABBOTT TED W
ABBOTT WILLIAM E
ABDON RYANN P
ABEL DIANE M
ABEL JUDITH D
ABERCROMBIE WILLIAM
ABERNATHY ALLISON
ABERNETHY JAMES T
ABINGTON S R
ABLES RALPH R
ABNER KERRY L
ABNEY PAMELA J
ABRAHAM WARREN MARGARET
ABRAHAMSEN DIANE SUE
ABRAHAMSEN ELIZABETH
ABRAHAMSEN MELISSA D
ABRAHAMSEN MICHELLE
ABRAHAMSON MATT
ABRAM ANNETTE
ABRAMS ABRAXAS D
ABRAMS BARBARA
ABRAMS ROSE M
ABSHIRE ERIC A
ABUAN MARIA P
ACCARDI AMY-JO
ACEVEDO ANGEL R
ACEVES MARIA D NUNEZ



RESULTS


S CONSTRUCTION INC
S CONSTRUCTION INC
S CONSTRUCTION INC
AABY TAMARA J
AADNESEN MARJORIE
AARANT MAUREEN R
AARON MICHELLEE
AARON PATRICIA J
ABBA DAVID W
ABBATE ANTHONY P
ABBOTT CHARLES C
ABBOTT FRED
ABBOTT JAMES P
ABBOTT JULIA L
ABBOTT KEITH E
ABBOTT MIKE E
NULL NULL
ABBOTT REALTY
ABBOTT REALTY
ABBOTT ROBERT L
ABBOTT ROBERT M
ABBOTT SAKUE
ABBOTT STEVEN F
ABBOTT TED W
ABBOTT WILLIAM E
ABDON RYANN P
ABEL DIANE M
ABEL JUDITH D
ABERCROMBIE WILLIAM
ABERNATHY ALLISON
ABERNETHY JAMES T
ABINGTON S R
ABLES RALPH R
ABNER KERRY L
ABNEY PAMELA J
ABRAHAM WARREN MARGARET
ABRAHAMSEN DIANE SUE
ABRAHAMSEN ELIZABETH
ABRAHAMSEN MELISSA D
ABRAHAMSEN MICHELLE
ABRAHAMSON MATT
ABRAM ANNETTE
ABRAMS ABRAXAS D
ABRAMS BARBARA
ABRAMS ROSE M
ABSHIRE ERIC A
ABUAN MARIA P
ACCARDI AMY-JO
ACEVEDO ANGEL R
MARIA D NUNEZ


As you can see, the first record is missing the first character and there is one record showing NULL NULL. Thanks for all the help!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 15:12:05
I don't see any NULL NULL rows in your last post. But anyway, try this:



SET NOCOUNT ON

DECLARE @Table table (Col001 varchar(100) NOT NULL)

INSERT INTO @Table VALUES('AABY TAMARA J')
INSERT INTO @Table VALUES('AADNESEN MARJORIE')
INSERT INTO @Table VALUES('AARANT MAUREEN R')
INSERT INTO @Table VALUES('AARON MICHELLEE')
INSERT INTO @Table VALUES('AARON PATRICIA J')
INSERT INTO @Table VALUES('ABBA DAVID W')
INSERT INTO @Table VALUES('A S CONSTRUCTION INC')

SELECT
Column1 =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL THEN ''
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 4)
END,

LName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 2)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 3)
END,
FName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 1)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 2)
END,
MName =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN ''
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 1)
END
FROM @Table




Wasn't sure how you wanted to handle 4 part ones...

Tara
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 15:17:18
Perfect! You are the SQL Queen! Thanks soooooo much : )
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-12-29 : 15:25:48
No problem.

Here's another one. I restructured the columns:



SET NOCOUNT ON

DECLARE @Table table (Col001 varchar(100) NOT NULL)

INSERT INTO @Table VALUES('AABY TAMARA J')
INSERT INTO @Table VALUES('AADNESEN MARJORIE')
INSERT INTO @Table VALUES('AARANT MAUREEN R')
INSERT INTO @Table VALUES('AARON MICHELLEE')
INSERT INTO @Table VALUES('AARON PATRICIA J')
INSERT INTO @Table VALUES('ABBA DAVID W')
INSERT INTO @Table VALUES('A S CONSTRUCTION INC')

SELECT
Column1 =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL AND PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 2)
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 3)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 4)
END,
Column2 =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL AND PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 1)
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 2)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 3)
END,
Column3 =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL AND PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN ''
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL THEN PARSENAME(REPLACE(Col001, ' ', '.'), 1)
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 2)
END,
Column4 =
CASE
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL AND PARSENAME(REPLACE(Col001, ' ', '.'), 3) IS NULL THEN ''
WHEN PARSENAME(REPLACE(Col001, ' ', '.'), 4) IS NULL THEN ''
ELSE PARSENAME(REPLACE(Col001, ' ', '.'), 1)
END
FROM @Table



Tara
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 15:31:00
I can see where the NULL NULL is coming from. The 17th record is in 5 parts (ABBOTT PAUL R LEE H
). Is there anything I can do with records like that? Sorry for being a pain.
Go to Top of Page

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-29 : 15:33:52
You're only limitted to 4 elements if you want to use parsename
If you use
http://www.mindsdoor.net/SQLTsql/f_GetEntryDelimiitted.html

then the query becomes

insert tbl
select
dbo.f_GetEntryDelimiitted(Col001, 1, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(Col001, 2, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(Col001, 3, ' ', 'N') ,
dbo.f_GetEntryDelimiitted(Col001, 4, ' ', 'N')
from mytbl

You can change f_GetEntryDelimiitted if you don't like what it returns if the field doesn't exist.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2004-12-29 : 17:44:41
Here is an ugly alternative to using the parsename function:

SET NOCOUNT ON

DECLARE @Table table (Col001 varchar(100) NOT NULL)

INSERT INTO @Table VALUES('ABBOTT PAUL R LEE H')
INSERT INTO @Table VALUES('AABY TAMARA J')
INSERT INTO @Table VALUES('AADNESEN MARJORIE')
INSERT INTO @Table VALUES('AARANT MAUREEN R')
INSERT INTO @Table VALUES('AARON MICHELLEE')
INSERT INTO @Table VALUES('AARON PATRICIA J')
INSERT INTO @Table VALUES('ABBA DAVID W')
INSERT INTO @Table VALUES('ABBATE ANTHONY P')


Select
[first] =
case
when charindex(' ', Col001) = 0 then Col001
Else subString(Col001, 1, charindex(' ', Col001))
End

,[Last] =
case
when charindex(' ', Col001) = 0 then null
when charindex(' ', Col001, charindex(' ', Col001)+1) = 0 then subString(Col001, charindex(' ', Col001)+1, 50)
else subString(Col001, charindex(' ', Col001)+1, charindex(' ', Col001, charindex(' ', Col001)+1) - charindex(' ', Col001))
end

,[Middle] = --this leaves everything after 2nd space combined ie: R LEE H
case
when charindex(' ', Col001) = 0 then null
when charindex(' ', Col001, charindex(' ', Col001)+1) = 0 then null
else convert(varChar(25), substring(Col001, charindex(' ', Col001, charindex(' ',Col001)+1)+1, 50))
end
From @Table



TG
Go to Top of Page

Frank Kalis
Constraint Violating Yak Guru

413 Posts

Posted - 2004-12-30 : 03:33:02
Hi ya, seems like you've posted this to more than one community
Should I repeat my reply from SQL Server Central or will you read it there?

--
Frank
http://www.insidesql.de
Go to Top of Page

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-30 : 07:48:26
Frank, I read all my posts. I am trying to get a viable solution to this unique problem. Tara, NR and TG have been a big help and I really appreaciate the effort.
Go to Top of Page
    Next Page

- Advertisement -