August 28, 2012 at 2:03 am
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
August 28, 2012 at 2:23 am
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.
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
August 28, 2012 at 5:54 am
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?
August 28, 2012 at 6:01 am
Will the names always be in the same format TITLE/FIRST/SECOND, or could you have middle names & missing titles, for example?
August 28, 2012 at 6:03 am
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]
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
August 28, 2012 at 6:08 am
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
August 28, 2012 at 6:25 am
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?
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
August 28, 2012 at 6:32 am
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
August 28, 2012 at 6:34 am
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.
??
August 28, 2012 at 6:37 am
amarkhowe (8/28/2012)
SELECTAccountNumber,
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.
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
August 28, 2012 at 6:44 am
I'v attached an Excel file with test data
Many Thanks
Andy
August 28, 2012 at 6:51 am
amarkhowe (8/28/2012)
I'v attached an Excel file with test dataMany 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.
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
August 28, 2012 at 7:00 am
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
August 28, 2012 at 7:06 am
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
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
August 28, 2012 at 7:19 am
Thanks for your help I have been able to get Chris's sql to work,
Thanks for all your help.
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply