RE: Split a name on to new columns in a table

SSC Guru

Points: 186127

August 28, 2012 at 6:37 am

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