Seperate Field with slashes - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Seperate Field with slashes

Author

Topic

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-04 : 12:03:06

If I have a field called MEMBER_NAME with:

Bosco\James\A

How do I Select LAST_NAME, FIRST_NAME, MI in a substring?

whitefang
Enterprise-Level Plonker Who's Not Wrong

272 Posts

Posted - 2009-05-04 : 12:06:30

SELECT REPLACE([column], '\',' , ') AS name FROM [table]

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-04 : 12:12:26

Ok I am sorry how do Split MEMBER_NAME into 3 columns. LAST_NAME, FIRST_NAME, MI.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-05-04 : 13:11:48

declare @name varchar(50)
set @name = 'Bosco\James\A'

select replace(@name,'\','.')

select parsename(replace(@name,'\','.'),3)
,parsename(replace(@name,'\','.'),1)
,parsename(replace(@name,'\','.'),2)

Jim

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-04 : 13:51:05

Thank you very much Jim and whitefang I was able to get it to work!!!!!

jbosco1988
Starting Member

46 Posts

Posted - 2009-05-04 : 17:38:51

What if I have multiple records not just:

Bosco\James\A
Jones\Sam\B
smith\Steven

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-05 : 00:35:06

[code]
declare @tab table(personname varchar(128))
insert into @tab select 'Bosco\James\A'
insert into @tab select 'Jones\Sam\B'
insert into @tab select 'smith\Steven'

select parsename(replace(personname,'\','.'),3)
,parsename(replace(personname,'\','.'),1)
,parsename(replace(personname,'\','.'),2)
from @tab

select left(personname,charindex('\',personname,1)-1) firstname,
right(personname,charindex('\',reverse(personname),1)-1) middlename,
case when charindex('\',personname,charindex('\',personname,1)+1) > 0 then substring(personname,charindex('\',personname,1)+1,len(personname)-
(len(left(personname,charindex('\',personname,1)))+len(right(personname,charindex('\',reverse(personname),1))))) end as lastname
from @tab
[/code]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-05 : 01:11:45

Also notive that the PARSENAME approach above will fail if you have for example "Jr." in the data.
See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76033
for a split function dbo.fnParseString or dbo.fnParseList.

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources