Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2008 » SQL Server Newbies » Split a name on to new columns in a table
Add to briefcase
22 posts, Page 1 of 3
123»»»

Split a name on to new columns in a table Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
amarkhowe
Posted Tuesday, August 28, 2012 2:03 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 1:50 AM
Points: 14, Visits: 13
Monring,

If I have a name all in one cell on a column such as
"Mr Simon ASHWORTH"
How can I split the name so that I could have Mr on a column called title, Simon on a column called Fist Name and ASHWORTH on a column called Last Name?

Many Thanks,

Andy
Post #1350717
ChrisM@Work
Posted Tuesday, August 28, 2012 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
amarkhowe (8/28/2012)
Monring,

If I have a name all in one cell on a column such as
"Mr Simon ASHWORTH"
How can I split the name so that I could have Mr on a column called title, Simon on a column called Fist Name and ASHWORTH on a column called Last Name?

Many Thanks,

Andy


DROP TABLE #NamesAndAddresses
CREATE TABLE #NamesAndAddresses (Fullname VARCHAR(100))
INSERT INTO #NamesAndAddresses (Fullname) VALUES ('Mr Simon ASHWORTH')

SELECT 
	Title		= LEFT(Fullname,space1.pos-1),
	Forename	= SUBSTRING(Fullname,space1.pos+1,space2.pos-space1.pos-1),
	Surname		= SUBSTRING(Fullname,space2.pos+1,8000)
FROM #NamesAndAddresses
CROSS APPLY (SELECT CHARINDEX(' ', Fullname,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Fullname,space1.pos+1)) space2 (pos)

Edit: slight change to code.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1350726
amarkhowe
Posted Tuesday, August 28, 2012 5:54 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 1:50 AM
Points: 14, Visits: 13
Thanks you for your reply!

I do not want to drop the table but split the name on to the same table in new columns?
Post #1350851
laurie-789651
Posted Tuesday, August 28, 2012 6:01 AM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 30, 2013 1:26 PM
Points: 278, Visits: 808
Will the names always be in the same format TITLE/FIRST/SECOND, or could you have middle names & missing titles, for example?
Post #1350856
ChrisM@Work
Posted Tuesday, August 28, 2012 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
amarkhowe (8/28/2012)
Thanks you for your reply!

I do not want to drop the table but split the name on to the same table in new columns?


That's just sample data.
Change the tablename in the query to the name of your table of names and addresses:

SELECT 
	Title		= LEFT(Fullname,space1.pos-1),
	Forename	= SUBSTRING(Fullname,space1.pos+1,space2.pos-space1.pos-1),
	Surname		= SUBSTRING(Fullname,space2.pos+1,8000)
FROM MarksTableOfNamesAndAddresses
CROSS APPLY (SELECT CHARINDEX(' ', Fullname,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Fullname,space1.pos+1)) space2 (pos)

[/code]

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1350857
amarkhowe
Posted Tuesday, August 28, 2012 6:08 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 1:50 AM
Points: 14, Visits: 13
Yes they would be so example:


Full Name
MR Peter ASHWORTH

would end up as

Full Name Title First Name Last Name
Mr Peter ASHWORTH Mr Peter ASHWORTH

Cheers
Post #1350861
ChrisM@Work
Posted Tuesday, August 28, 2012 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
amarkhowe (8/28/2012)
Yes they would be so example:


Full Name
MR Peter ASHWORTH

would end up as

Full Name Title First Name Last Name
Mr Peter ASHWORTH Mr Peter ASHWORTH

Cheers


What's the name of your table? What are the names of the columns for the three new data elements?

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1350876
amarkhowe
Posted Tuesday, August 28, 2012 6:32 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 1:50 AM
Points: 14, Visits: 13
SELECT AccountNumber, AccountName
From dbo.Account

Field 1 =
AccountNumber
200000003

Field 2 =
AccountName
Mr Peter ASHWORTH

I want to split the Account Name into three new fields Title, First_Name, Last_Name but Keep both the original fields (AccountNumber and AccountName).

Cheers
Post #1350882
amarkhowe
Posted Tuesday, August 28, 2012 6:34 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 29, 2012 1:50 AM
Points: 14, Visits: 13
SELECT
AccountNumber,
Name,
Name = LEFT(Name,space1.pos-1),
Name = SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),
Name = SUBSTRING(Name,space2.pos+1,40)
FROM dbo.AccountBase
CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

But the above creates an error

Msg 537, Level 16, State 3, Line 5
Invalid length parameter passed to the LEFT or SUBSTRING function.

??
Post #1350884
ChrisM@Work
Posted Tuesday, August 28, 2012 6:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:57 AM
Points: 5,705, Visits: 11,140
amarkhowe (8/28/2012)
SELECT
AccountNumber,
Name,
Name = LEFT(Name,space1.pos-1),
Name = SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),
Name = SUBSTRING(Name,space2.pos+1,40)
FROM dbo.AccountBase
CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)
CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

But the above creates an error

Msg 537, Level 16, State 3, Line 5
Invalid length parameter passed to the LEFT or SUBSTRING function.

??


You have names with only one space in them. You said:
amarkhowe (8/28/2012)
Yes they would be so example:


Full Name
MR Peter ASHWORTH

would end up as

Full Name Title First Name Last Name
Mr Peter ASHWORTH Mr Peter ASHWORTH

Cheers


Did you check?

Can you set up some sample data for us? The link in my sig shows you how to do this.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1350885
« Prev Topic | Next Topic »

Add to briefcase
22 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.