T-SQL "unconcatenate" Column

I have a SQL Server table that looks like this:

ID     NAME    
1      John Dough
2      Donald Disney
3      Brajalenia Jollie-Pitt
4      A. Smith
5      James Watson

I need to take the name and split into two columns; First Name and Last Name. Sadly, I know nothing about access. Can anyone help me with such a query?

PS - My columns have no Middle Initials and/or suf/prefixes
LVL 3
computerstreberAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
select
   Left([Name],charindex(' ',[name],1) - 1) as FirstName
  ,Right(Name],DataLength([Name]-charindex(' ',[name],1)) as LastName

From YourTable
Where [Name] like '% %'
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
computerstreberAuthor Commented:
I got this error:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'Snow White' to data type int.
0
LowfatspreadCommented:
select
   Left([Name],charindex(' ',[name],1) - 1) as FirstName
  ,Right([Name],DataLength([Name])-charindex(' ',[name],1)) as LastName

From YourTable
Where [Name] like '% %'
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

computerstreberAuthor Commented:
select
Left([tName],charindex(' ',[tname],1) - 1) as FirstName
  ,Right([tName],DataLength([tName])-charindex(' ',[tname],1)) as LastName
  from test

comes out like this:

FirstName     LastName
Alpha            Alpha Beta
0
LowfatspreadCommented:
what is the datatype of tName?
if it is Nchar or Nvarchar

then

select
Left([tName],charindex(' ',[tname],1) - 1) as FirstName
  ,Right([tName],(DataLength([tName])/2)-charindex(' ',[tname],1)) as LastName
  from test
where tname like '% %'


since datalength(..) gives the number of bytes not characters
0
dan_masonCommented:
Lowfatspread, what's the reasoning behind using DataLength here rather than LEN, which would work for either varchar or nvarchar?
0
LowfatspreadCommented:
.
trailing blanks   in the name column are important...
len would not count them and so the Right function would cut at the wrong place...
unless we embed the column in trim(s) which is another depth of function i feel is unnecessary....
0
dan_masonCommented:
I see, thanks.
0
computerstreberAuthor Commented:
Thanks!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.