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

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

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.

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!

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

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

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

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

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"

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

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

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.

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!

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

cgunner
Yak Posting Veteran

95 Posts

Posted - 2004-12-29 : 15:17:18

Perfect! You are the SQL Queen! Thanks soooooo much : )

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

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.

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.

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

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

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.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources