Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 separate string

Author  Topic 

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-02 : 16:14:26
Hi All.

How to separate column FullName to three column LastName, FirstName, and MI? Sample of FullName - Smith, John P.

Thanks

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-03 : 03:51:02
use charindex function to find delimiters (comma and space in your sample). Use results of charindex in substring to get the part you want.
Go to Top of Page

eugz
Posting Yak Master

210 Posts

Posted - 2015-03-03 : 09:35:04
Hi gbritton. Thanks for replay.
I know how to separate FullName look like (LastName, FirstName) but I have a problem to separate FullName including middle initial which look like (LastName, FirstName MI.).

Thanks.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-03 : 12:13:04
OK, this is kind of verbose, but I want to illustrate the process step-by-step. basically you search the whole string for the comma to get the last name and what's left. Then, you split what's left by the space.

declare @fn varchar(100) = 'Smith, John P.'

select f.n , sur.name LastName, _1st.name FirstName, middle.initials MiddleInitials
from (values (@fn)) f(n)
cross apply (
select charindex(',', f.n)) the(comma)
cross apply (
select left(f.n, the.comma-1)) sur(name)
cross apply (
select ltrim(right(f.n, len(f.n) - the.comma))) name(andinitial)
cross apply (
select charindex(' ', name.andinitial)) _space(_between)
cross apply (
select left(name.andinitial, _space._between-1)) _1st(name)
cross apply (
select right(name.andinitial, len(name.andinitial) - _space._between)) middle(initials)
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2015-03-10 : 02:35:33
If it is always three parts, you can also use parsename function also


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -