Split a name on to new columns in a table

Question

Split a name on to new columns in a table

amarkhowe

Old Hand

Points: 304
More actions
August 28, 2012 at 2:03 am

#265942

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

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

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

amarkhowe Old Hand Points: 304 More actions · Answer 2

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?

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 3

Will the names always be in the same format TITLE/FIRST/SECOND, or could you have middle names & missing titles, for example?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 4

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

amarkhowe Old Hand Points: 304 More actions · Answer 5

Yes they would be so example:

Full Name

MR Peter ASHWORTH

would end up as

Full Name TitleFirst NameLast Name

Mr Peter ASHWORTHMrPeter ASHWORTH

Cheers

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

amarkhowe (8/28/2012)
Yes they would be so example:
Full Name
MR Peter ASHWORTH
would end up as
Full Name TitleFirst NameLast Name
Mr Peter ASHWORTHMrPeter 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

amarkhowe Old Hand Points: 304 More actions · Answer 7

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

amarkhowe Old Hand Points: 304 More actions · Answer 8

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.

??

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

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 TitleFirst NameLast Name
Mr Peter ASHWORTHMrPeter 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

amarkhowe Old Hand Points: 304 More actions · Answer 10

I'v attached an Excel file with test data

Many Thanks

Andy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

amarkhowe (8/28/2012)
I'v attached an Excel file with test data
Many Thanks
Andy

Andy, if you set this up as a DDL script CREATE TABLE ... and INSERT INTO TABLE ..., then paste it into a post using the sql code tags, folks will work on the code for you.

^{“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

Ant-Green SSC Guru Points: 113445 More actions · Answer 12

DECLARE @account TABLE (AccountNo INT, Name VARCHAR(100))

INSERT INTO @account VALUES

(60000000,'Mrs Rayford Hufflepuff18'),

(60000001,'Miss Forest Hufflepuff18'),

(60000061,'Miss Bex Ignore'),

(60000068,'Mr Jason Twirl'),

(60000088,'Mrs Katuta Sunger'),

(60000111,'Mrs Corene Ravenclaw19'),

(60000112,'Miss Reta Ravenclaw19'),

(60000132,'Miss Aubrey Ravenclaw19'),

(60000137,'Mr Millicent Ravenclaw19'),

(60000207,'Brigadier Hannah Decorationbextest'),

(60000220,'Professor Jerome Smithe')

SELECT

AccountNo,

Name,

Title= LEFT(Name,space1.pos-1),

Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

Surname= SUBSTRING(Name,space2.pos+1,8000)

FROM @account

CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)

CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

Above works and is a copy of Chris's original answer, so there must be a problem in your data in the AccountBase table.

Data like the below will error with invalid length as there is no second space in the string.

E.g.

Mr Andy

Mr AnthonyGreen

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 13

You can detect name strings with less than three words with a slight modification of the original query:

SELECT

AccountNo,

Name,

space1.pos,

space2.pos

--Title= LEFT(Name,space1.pos-1),

--Forename= SUBSTRING(Name,space1.pos+1,space2.pos-space1.pos-1),

--Surname= SUBSTRING(Name,space2.pos+1,8000)

FROM @account

CROSS APPLY (SELECT CHARINDEX(' ', Name,1)) space1 (pos)

CROSS APPLY (SELECT CHARINDEX(' ', Name,space1.pos+1)) space2 (pos)

WHERE ISNULL(space1.pos,0) = 0 OR ISNULL(space2.pos,0) = 0

^{“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

amarkhowe Old Hand Points: 304 More actions · Answer 14

Thanks for your help I have been able to get Chris's sql to work,

Thanks for all your help.