Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Split a name on to new columns in a table Expand / Collapse
Author
Message
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
Posted Tuesday, August 28, 2012 2:23 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 7,230, Visits: 13,709
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
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
Posted Tuesday, August 28, 2012 6:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
Will the names always be in the same format TITLE/FIRST/SECOND, or could you have middle names & missing titles, for example?

Post #1350856
Posted Tuesday, August 28, 2012 6:03 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 7,230, Visits: 13,709
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
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
Posted Tuesday, August 28, 2012 6:25 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 7,230, Visits: 13,709
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
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
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
Posted Tuesday, August 28, 2012 6:37 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:55 AM
Points: 7,230, Visits: 13,709
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 123»»»

Permissions Expand / Collapse