[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 802
  • Last Modified:

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
0
computerstreber
Asked:
computerstreber
  • 4
  • 3
  • 2
4 Solutions
 
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
 
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 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now