Returning three Substrings from one field

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)

Returning three Substrings from one field

Author

Topic

cdraves
Starting Member

2 Posts

Posted - 2009-04-13 : 10:24:01

This may be an esay question but I need a little help.

I have a field that has the following information:

Benefit \ Benefit Cards Request \ Dental

I want to break this information into three fields:

Field 1: Benefit
Field 2: Benefit Cards Request
Field 3: Dental

I know I can use:

charindex(' \ ',DNACare_keys.KeyOptionName)

to get the first part. But how would you get the information in the middle and on the end?

I see that I may need to use a function. But is there any other way?

Thanks, Carl

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-13 : 10:31:00

declare @s varchar(100)
set @s='Benefit \ Benefit Cards Request \ Dental'
set @s=replace(@s,'\','.')

select parsename(@s,3),parsename(@s,2),parsename(@s,1)

Madhivanan

Failing to plan is Planning to fail

cdraves
Starting Member

2 Posts

Posted - 2009-04-13 : 11:08:35

This worked great.

Thank you.

Carl

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-04-14 : 07:28:52

try this one too
declare @s varchar(100)
set @s='Benefit \ Benefit Cards Request \ Dental'

select substring(@s,1,charindex('\',@s,1)-1),
substring(@s,charindex('\',@s,1)+1,(charindex('\',@s,charindex('\',@s,1)+1)-charindex('\',@s,1))-1),
right(@s,charindex('\',reverse(@s),1)-1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-15 : 02:56:23

quote:
Originally posted by cdraves

This worked great.

Thank you.

Carl

You are welcome

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources