Substring ??? - 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 2000 Forums

Transact-SQL (2000)

Substring ???

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-19 : 10:56:08

Donna writes "I have a table with a column "MemberName" the field length is 32, I want to extract the Lastname, Firstname and MI. There is a "\" in between the name like so: DOE\JANE\I. It's driving me nuts... I use the following in my select statment:

substring(MEMBNM,charindex('\',MEMBNM)+1 ,len(membnm)) as [First Name]

and receive the following results

JANE\I

How do I get rid of the last "\" and the middle initial "I".

I know I am close but I can't see it. Can you help the newbie???"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-04-19 : 11:14:33

declare @n varchar(50)
select @n = 'DOE\JANE\I'
select parsename(replace(@n,'\','.'),1)
select parsename(replace(@n,'\','.'),2)
select parsename(replace(@n,'\','.'),3)

Jay White
{0}

dreameR.78
Starting Member

3 Posts

Posted - 2004-04-19 : 11:21:32

and if you really want to use a combination of substrings and charindexes


declare @MEMBNM varchar(100)

set @MEMBNM = 'DOE\JANE\I'

select
	left(@MEMBNM, charindex('\', @MEMBNM)-1) as 'Last Name',	
	right(@MEMBNM, charindex('\', reverse(@MEMBNM))-1) as 'Initials',
	substring(@MEMBNM, charindex('\', @MEMBNM)+1, len(@MEMBNM) - 
	(charindex('\', reverse(@MEMBNM)) + charindex('\', @MEMBNM))) as 'Forename'

_________________
Reality of a dreameR

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources