March 28, 2008 at 1:27 pm
Guys,
I have to split fullname into first name and last name - I was not able to do it substring is there any to do this?
Name Fname Lname
______________________________________
smith, john john smith
doe, john john doe
Any suggestions and inputs would help
Thanks
March 28, 2008 at 1:30 pm
So you've already given it a shot with SUBSTRING? Why don't you post what you tried so we can have a look.
March 28, 2008 at 2:30 pm
Got it to work with substring and patindex
SET FNAME = SUBSTRING(displayname, PATINDEX('%,%', displayname) + 2, DATALENGTH(displayname)),
LNAME = SUBSTRING(displayname, 1, PATINDEX('%,%', displayname) - 1)
March 28, 2008 at 8:38 pm
You might be able to start with something similar to:
declare @test-2 table(Name varchar(15))
insert into @test-2
select 'smith, john' union all select 'doe, john'
select
Name,
substring(name, charindex(',', name) + 1, 15) as FName,
left(name, charindex(',', name) - 1) as LName
from @test-2
/* -------- Sample Output: --------
Name FName LName
--------------- --------------- ---------------
smith, john john smith
doe, john john doe
*/
I don't consider to this be complete because you might not always have a comma or have to deal with other data variations. It is likely that both your FName and LName portions will need to include case statements to provide for data variations.
Theoretically, you might also be able to apply the PARSENAME function with the REPLACE function with something like:
select
Name,
parsename(replace(name,',','.'), 1) as FName,
parsename(replace(name,',','.'), 2) as LName
from @test-2
However, the PARSENAME method is bound to be way less bullet proof and I really think you are better off using CHARINDEX and SUBSTRING.
I hope that helps get you started.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply