Charindex - 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

Charindex

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2012-02-09 : 09:29:25

Hi All,

I have the following field , identity_data which is displayed in the database as follows:
identity_code=5793#ANDERSKA
identity_code=4897#selkins&rccode=invalid
identity_code=5671#dsharabb&rccode=invalid
I want to display the characters after # and end before the &
namely :ANDERSKA,selkins,dsharabb
I have the below code:
CASE WHEN charindex('code=',o.identity_data) > 0
THEN CASE WHEN charindex('=',o.identity_data,charindex('code=',o.identity_data)) > 1
THEN SUBSTRING(o.identity_data, CHARINDEX('#',o.identity_data)+1, 6)
END
ELSE substring(o.identity_data,5,6)
END AS Identifier,

This displays the following:ANDERS,selkin,dshara

I need to get the complete string ending before& and the length of the string varies.

Thanks for your help in advance,
Petronas

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-09 : 09:37:09

It may be simpler to use STUFF function like this:


STUFF
(
	COALESCE(STUFF(o.identity_data,CHARINDEX('&',o.identity_data),LEN(o.identity_data),''),o.identity_data),
	1, CHARINDEX('#',o.identity_data),''
)

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-02-09 : 10:11:02

Don't know what is "simpler" to understand... here are both

declare @Sample table (data varchar(255))
insert @Sample
select 'identity_code=5793#ANDERSKA' union all
select 'identity_code=4897#selkins&rccode=invalid' union all
select 'identity_code=5671#dsharabb&rccode=invalid'


select 
data,
case
	when charindex('#',data) > 1
	then substring(data,
	               charindex('#',data)+1,
	               case when (charindex('&',data)-charindex('#',data))-1 >0 
	                    then (charindex('&',data)-charindex('#',data))-1 
	                    else len(data)-charindex('#',data)
	               end )
end as [DoneWithCharindex],
STUFF
(
	COALESCE(STUFF(data,CHARINDEX('&',data),LEN(data),''),data),
	1, CHARINDEX('#',data),''
) as [DoneWithStuff]
from @Sample

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-09 : 10:19:06

quote:
Originally posted by webfred

Don't know what is "simpler" to understand... here are both

I should have said "fewer keystrokes" :)

Petronas
Posting Yak Master

134 Posts

Posted - 2012-02-09 : 10:40:37

Thank you for the solution. It worked perfectly with STUFF. Appreciate you both for spending your valuable time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources