Substring Query - 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 Query

Author

Topic

miranwar
Posting Yak Master

125 Posts

Posted - 2006-12-21 : 16:18:09

I have a table wich contains a string containing data like this

'www.Site.com Moduleid=3@CampaignId=78,S'
www.Site.com Moduleid=4@CampaignId=78,S'
'www.Site.com Moduleid=4'

I am trying to extract the numbers for module id. i have come up with the following SQL. Hoever it fails if module id is the last string item

'www.Site.com Moduleid=4'

Can I make this more dynamic so that it caters for all formats, regardless of the moduleid item in the string:

SELECT top 20 id, QUERYSTRING as 'OriginalString',
CASE
WHEN len(querystring) > 1 THEN SUBSTRING(querystring , CHARINDEX ( 'moduleid=' , querystring , 1 ) +9 , ((CHARINDEX('&',querystring,CHARINDEX ( 'moduleid=' , querystring , 1 )+9)) - (CHARINDEX ( 'moduleid=' , querystring , 1 )+9)))

ELSE NULL
END, moduleID
FROM IIS_data

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-12-22 : 00:49:15

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=51476
http://weblogs.sqlteam.com/brettk/archive/2005/06/22/6328.aspx

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-22 : 06:12:03

[code]-- prepare test data
declare @t table (data varchar(150))

insert @t
select 'www.Site.com Moduleid=3@CampaignId=78,S' union all
select 'www.Site.com Moduleid=4@CampaignId=78,S' union all
select 'www.Site.com Moduleid=4234 @CampaignId=78,S' union all
select 'www.Site.com Moduleid=@CampaignId=78,S' union all
select 'www.Site.com CampaignId=78,S' union all
select 'www.Site.com Moduleid=4'

-- do the work
select m.data,
case when m.length = 0 then substring(m.data, m.fpos, 8000) else substring(m.data, m.fpos, m.length - 1) end [ModuleID]
from (
select data,
fpos,
patindex('%[^0-9]%', substring(data, fpos, 8000)) length
from (
select data,
9 + charindex('ModuleID=', data) fpos
from @t
) k
) m[/code]

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources