Split name field into 3 fields, please help me

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 2005 Forums

Transact-SQL (2005)

Split name field into 3 fields, please help me

Author

Topic

rtpnc
Starting Member

19 Posts

Posted - 2007-07-31 : 13:06:26

Well we have a full name field that has FirstName, Middle name or Initial, Last Name. Right now our query does not work well because of the full name field. We thought we should break this field into three fields Firstname, middlename, lastname.

We are new to sql (of course) so if anyone can help us out that would be very very helpful.

This table is updated every night by a csv file ftped to our server.

The table has 400-500K rows on average.

Thanks again.

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-31 : 13:10:06

It would be helpfuil to see some examples (read the hint link in my sig)...but I suspect not everyone has a MI

Also what about stuff like Davis J Love III

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rtpnc
Starting Member

19 Posts

Posted - 2007-07-31 : 13:23:50

Ok,

CREATE TABLE [dbo].[tblRORE7] (
[NAME1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NAME2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDR1] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ADDR2] [nvarchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CITY] [nvarchar] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[STA] [nvarchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIPC] [nvarchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

NAME1 - EXAMPLES
BOUFFARD GERALDINE BUTLER
BOLES JOHN H
BOLES JOHN H
SNODY DEWEY SANFORD
SNODY DEWEY SANFORD
SNODY DEWEY SANFORD
SNODY DEWEY SANFORD
SNODY DEWEY SANFORD
SNODY DEWEY SANFORD
HANKS JERRY LEE
HANKS JERRY LEE
SPARKS CAROL HALL
SPARKS CAROL HALL
HANKS MARY ADKINS
HARRIS CHARLIE ROBERT
HARRIS CHARLIE ROBERT
PATTERSON NINA WILLIAMS
PATTERSON NINA WILLIAMS
PATTERSON SANDRA DARE
STANLEY NANNIE FAIN
STANLEY NANNIE FAIN
STANLEY NANNIE FAIN
STANLEY NANNIE FAIN
HARVEY MICHELLE DEMETRIA
HARVEY MICHELLE DEMETRIA

We are really only interested in the first name and lastname.

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-31 : 13:28:50

What's with the duplicates?

In any Case.I'm assuming the last name is first, and the 2nd name in the string is the first name, and the third is the Middlename

Correct?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2007-07-31 : 13:44:10

[code]
CREATE TABLE #myTemp99(Col1 varchar(8000))
GO

INSERT INTO #myTemp99(Col1)
SELECT 'BOUFFARD GERALDINE BUTLER'AS FULLNAME UNION ALL
SELECT 'BOLES JOHN H'AS FULLNAME UNION ALL
SELECT 'BOLES JOHN H'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'SNODY DEWEY SANFORD'AS FULLNAME UNION ALL
SELECT 'HANKS JERRY LEE'AS FULLNAME UNION ALL
SELECT 'HANKS JERRY LEE'AS FULLNAME UNION ALL
SELECT 'SPARKS CAROL HALL'AS FULLNAME UNION ALL
SELECT 'SPARKS CAROL HALL'AS FULLNAME UNION ALL
SELECT 'HANKS MARY ADKINS'AS FULLNAME UNION ALL
SELECT 'HARRIS CHARLIE ROBERT'AS FULLNAME UNION ALL
SELECT 'HARRIS CHARLIE ROBERT'AS FULLNAME UNION ALL
SELECT 'PATTERSON NINA WILLIAMS'AS FULLNAME UNION ALL
SELECT 'PATTERSON NINA WILLIAMS'AS FULLNAME UNION ALL
SELECT 'PATTERSON SANDRA DARE'AS FULLNAME UNION ALL
SELECT 'STANLEY NANNIE FAIN'AS FULLNAME UNION ALL
SELECT 'STANLEY NANNIE FAIN'AS FULLNAME UNION ALL
SELECT 'STANLEY NANNIE FAIN'AS FULLNAME UNION ALL
SELECT 'STANLEY NANNIE FAIN'AS FULLNAME UNION ALL
SELECT 'HARVEY MICHELLE DEMETRIA'AS FULLNAME UNION ALL
SELECT 'HARVEY MICHELLE DEMETRIA' AS FULLNAME
GO

SELECT DISTINCT
'"'+SUBSTRING(Col1,1,POS1-1)+'"' AS Last_Name
, '"'+SUBSTRING(Col1,POS1+1,POS2-POS1-1)+'"' AS First_Name
FROM (
SELECT Col1
, CHARINDEX(' ',Col1) AS POS1
, CHARINDEX(' ',Col1,CHARINDEX(' ',Col1)+1) AS POS2
FROM #myTemp99) AS XXX
GO

DROP TABLE #myTemp99
GO

[/code]

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Kristen
Test

22859 Posts

Posted - 2007-07-31 : 13:44:27

Assuming that your [NAME1] column has 3 "pieces" of data, and they are separated by a space; and that you add three NEW columns for the individual pieces of data, then the following will update the table and "split" the 3 pieces of data.


DECLARE	@I1	int,
	@I2	int,
	@I3	int

UPDATE U
SET
	  @I1 = CHARINDEX(' ', NAME1 + ' ')
	, [NEW_COLUMN_1] = LEFT(NAME1, @I1-1)
	, @I2 =  NullIf(CHARINDEX(' ', NAME1 + ' ', @I1+1), 0)
	, [NEW_COLUMN_2] = SUBSTRING(NAME1, @I1+1, @I2-@I1-1)
	, @I3 =  NullIf(CHARINDEX(' ', NAME1 + ' ', @I2+1), 0)
	, [NEW_COLUMN_3] = SUBSTRING(NAME1, @I2+1, @I3-@I2-1)
FROM	dbo.tblRORE7 AS U

Backup first!!

Kristen

rtpnc
Starting Member

19 Posts

Posted - 2007-07-31 : 14:18:00

Kristen that worked so good, thank you so much!

The other thing is now we get a new updated csv file on our server everynight. We run a dts package that runs this file and puts the data in the table but now we have these 3 new rows ( fname, lname and mname) how do we account for these when we run the update every night?

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-08-01 : 01:43:41

I would either run a scheduled job after the DTS import (or as part of it), or create a Trigger on that table that will populate the extra 3 columns

Kristen

tanaka
Starting Member

1 Post

Posted - 2011-05-19 : 01:50:09

CREATE TABLE TEMPNAME
(NAME1 CHAR(100),
NEW_COLUMN_1 CHAR(100),
NEW_COLUMN_2 CHAR(100),
NEW_COLUMN_3 CHAR(100))

UPDATE TEMPNAME
SET NEW_COLUMN_1 = RTRIM(LTRIM(SUBSTRING(NAME1,1,LEN(NAME1))))

UPDATE TEMPNAME
SET NEW_COLUMN_2 = RTRIM(LTRIM(SUBSTRING(NEW_COLUMN_1,CHARINDEX(' ',NEW_COLUMN_1),LEN(NEW_COLUMN_1))))

UPDATE TEMPNAME
SET NEW_COLUMN_3 = RTRIM(LTRIM(SUBSTRING(NEW_COLUMN_2,CHARINDEX(' ',NEW_COLUMN_2),LEN(NEW_COLUMN_2))))

UPDATE TEMPNAME
SET NEW_COLUMN_1 = REPLACE(NEW_COLUMN_1,NEW_COLUMN_2,'') ,
NEW_COLUMN_2 = REPLACE(NEW_COLUMN_2,NEW_COLUMN_3,'')

UPDATE TEMPNAME
SET NEW_COLUMN_2 = '',
NEW_COLUMN_3 = NEW_COLUMN_2
WHERE LEN(NEW_COLUMN_2) > 1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources