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 AND SUBSTRINB

Author  Topic 

gavakie
Posting Yak Master

221 Posts

Posted - 2008-01-03 : 17:04:10
I have my column and the info is.

UserID=176&StoreID=210
sometimes there is more between the two so im tring to use a CHARINDEX and Substring

Substring(Parameters, CHARINDEX('UserID', Parameters),3)
When I do this it brings back just the 'USE' I need it to bring back the number associated with it. In this case the 176 how do i get that number?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-03 : 17:39:08
Here is one UGLY way: (I'm sure a slicker way will follow)

This assumes there is always another parameter that follows userid.

select substring(parameters
,charindex('UserID=', parameters)+7
,charindex('&', parameters, charindex('UserID=', parameters)+7) - (charindex('UserID=', parameters)+7)
)
from (
select 'UserID=176&StoreID=210' as parameters
) a

output:
----------------------
176



EDIT:
Here is more UGLYNESS way that doesn't assume anything

select substring(parameters
,idxStart
,case when idxEnd > 0 then idxEnd - idxStart else len(parameters) - idxStart + 1 end
)
from (
select charindex('UserID=', parameters)+7 as idxStart
,charindex('&', parameters, charindex('UserID=', parameters)+7) as idxEnd
,parameters
from (
select 'UserID=176&StoreID=210' as parameters union all
select 'UserID=176' union all
select 'abcdef' union all
select 'StoreID=210&UserID=176'
) a
where charindex('UserID=', parameters) > 0
) a
output:
----------------------
176
176
176





Be One with the Optimizer
TG
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 07:46:03
This just reminds me this http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=94405

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
   

- Advertisement -