|
|
Posted by figital on 03/20/06 19:42
There may be a better way but this should work...
CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO
INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv'
UNION SELECT
'OtherID=1638256785230&TranID=12345'
SELECT
CASE WHEN
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
) = 0
THEN
RIGHT(QueryString,
LEN(QueryString)-
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-6
)
ELSE
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
END AS TransID
FROM QueryStringTest ORDER BY TransID
DROP TABLE QueryStringTest
Navigation:
[Reply to this message]
|