separate string - SQL Server Forums

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.

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.

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)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources