We r migrating some legacy data to a new system. In the old database (SQL Server 2000) I have one field "FULLNAME' for the person name. In the new system we will be having three fields First Name, Middle Name and LastName. How to write sql statement to split
the three names.
declare @fullname varchar(100)
set @fullname='Yogesh R Bhadauriya' --assume name is separted by space
select substring(@fullname,0,charindex(' ',@fullname)),--first Name
substring(LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')),0,
CHARINDEX(' ',LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')))),--middlename
REPLACE(REPLACE(@FullName,substring(@fullname,0,charindex(' ',@fullname)),' '),
substring(LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')),0,
CHARINDEX(' ',LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')))),'')--last name
i'm always curious. i see a lot of the time i post an answer,, like this guy right here, posts the same link, or maybe it's not the correct answer but i post a sugguestion, and somebody down the line posts the same thing....it's so odd...i see it all the
time, i wonder if others that are helping on this forum actually read replies and questions or just simply rush on to the matter at hand. it's not always a link, sometimes it's code, custom code that one writes but it looks very similar. take a 2nd a read
what other people have said before you rush... just a thought =)
Please post your code for us to help!!
Mark Answered if it helps - Good luck!
Cheers!
Design And Align - Rob
Any way I found most of your answer guys were useful but I decide to do it using C# as I am not so good in transact SQL. With C# sharp I can manage the different cases like when there are more than one consecutive spaces, more than three names..etc.
Thank u robwscott for telling me about the method split in c#
itismesa
Member
232 Points
146 Posts
Split the Name into First Name, Middle Name and LastName
Feb 28, 2012 05:11 PM|LINK
Hi
We r migrating some legacy data to a new system. In the old database (SQL Server 2000) I have one field "FULLNAME' for the person name. In the new system we will be having three fields First Name, Middle Name and LastName. How to write sql statement to split the three names.
rgeards
robwscott
Star
8079 Points
1491 Posts
Re: Split the Name into First Name, Middle Name and LastName
Feb 28, 2012 06:12 PM|LINK
may try this link
http://stackoverflow.com/questions/159567/how-can-i-parse-the-first-middle-and-last-name-from-a-full-name-field-in-sql
for c#:
string[] fullName = yourVariable.Split( new Char[] { ' ' }); string firstName = fullName[0]; string middleName = fullName[1]; string lastName = fullName[2];Mark Answered if it helps - Good luck!
Cheers!
Design And Align
- Rob
itismesa
Member
232 Points
146 Posts
Re: Split the Name into First Name, Middle Name and LastName
Feb 28, 2012 06:34 PM|LINK
Can't we have done using SQL Statement directly?
vijay_myl
Contributor
5070 Points
1068 Posts
Re: Split the Name into First Name, Middle Name and LastName
Feb 29, 2012 04:42 AM|LINK
hi..Refer the below i hop it will help u
http://stackoverflow.com/questions/159567/how-can-i-parse-the-first-middle-and-last-name-from-a-full-name-field-in-sql
My .NET blog
Submit Article
yrb.yogi
Star
14460 Points
2402 Posts
Re: Split the Name into First Name, Middle Name and LastName
Feb 29, 2012 05:48 AM|LINK
declare @fullname varchar(100) set @fullname='Yogesh R Bhadauriya' --assume name is separted by space select substring(@fullname,0,charindex(' ',@fullname)),--first Name substring(LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')),0, CHARINDEX(' ',LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')))),--middlename REPLACE(REPLACE(@FullName,substring(@fullname,0,charindex(' ',@fullname)),' '), substring(LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')),0, CHARINDEX(' ',LTRIM(REPLACE(@fullname,substring(@fullname,0,charindex(' ',@fullname)),'')))),'')--last name.Net All About
shivv
Participant
1566 Points
283 Posts
Re: Split the Name into First Name, Middle Name and LastName
Feb 29, 2012 11:15 AM|LINK
Here is the solution:
declare @fullname varchar(100) set @fullname='FirstName MiddleName LastName' SELECT LEFT(@fullname, CharIndex(' ', @fullname) - 1) FirstName, SUBSTRING(@fullname, CharIndex(' ', @fullname), LEN(@fullname) - charindex(' ', REVERSE(@fullname)) - charindex(' ', @fullname) + 1) MiddelName, reverse(left(reverse(@fullname), charindex(' ', reverse(@fullname)) -1)) LastNamerobwscott
Star
8079 Points
1491 Posts
Re: Split the Name into First Name, Middle Name and LastName
Mar 01, 2012 06:05 AM|LINK
i'm always curious. i see a lot of the time i post an answer,, like this guy right here, posts the same link, or maybe it's not the correct answer but i post a sugguestion, and somebody down the line posts the same thing....it's so odd...i see it all the time, i wonder if others that are helping on this forum actually read replies and questions or just simply rush on to the matter at hand. it's not always a link, sometimes it's code, custom code that one writes but it looks very similar. take a 2nd a read what other people have said before you rush... just a thought =)
Mark Answered if it helps - Good luck!
Cheers!
Design And Align
- Rob
itismesa
Member
232 Points
146 Posts
Re: Split the Name into First Name, Middle Name and LastName
Mar 01, 2012 08:06 AM|LINK
yes I agree you posted first robwscott
Any way I found most of your answer guys were useful but I decide to do it using C# as I am not so good in transact SQL. With C# sharp I can manage the different cases like when there are more than one consecutive spaces, more than three names..etc.
Thank u robwscott for telling me about the method split in c#