By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,410 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,410 IT Pros & Developers. It's quick & easy.

Help with a substring query

P: n/a
I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.

Things you will need to keep in mind are;

The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.
Code=web.co.uk%product_250p&TranID=14078800&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=140777 70&OtherFlag0=services.web.stats.PostContentDelive ry&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Regards,
Ciarán

Mar 20 '06 #1
Share this Question
Share on Google+
8 Replies


P: n/a
ch********@hotmail.com wrote:
I need help capturing information from a free text field.
The 10 examples below contain examples of the information I am trying
to capture.
In each cell I am trying to capture the number between 'TranID=' and
the next '&'.
So in the fisrt cell I would like to capture 14078800.

Things you will need to keep in mind are;

The number is not of fixed length.
'TranID' will always precede the number
The number will always be followed by an '&'
The '&' sign can occur multiple times in the text.
Code=web.co.uk%product_250p&TranID=14078800&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherF lag0=services.web.stats.PostCSDelivery&OtherFlag0p ar=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=140777 70&OtherFlag0=services.web.stats.PostContentDelive ry&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherF lag0=services.web.stats.PostContentDelivery&OtherF lag0par=deliv


Hint: use LIKE.

Kind regards

robert
Mar 20 '06 #2

P: n/a
--something like this:
declare @tranid_position int, @amp_position int,@string varchar(8000),
@rest_of_string varchar(8000), @Result_string varchar(8000)
set
@string='Code=web.co.uk%product_free&TranID=140775 76&OtherFlag0=services.web.stats.P*ostCSDelivery&O therFlag0par=deliv
'
set @tranid_position=charindex('&TranID=',@string)
set @rest_of_string= substring(@string,@tranid_position+8,8000)
set @amp_position=charindex('&',@rest_of_string)
set @Result_string=left(@rest_of_string,@amp_position-1)
select @Result_string

Mar 20 '06 #3

P: n/a
Here you go...

CREATE TABLE QueryStringTest (QueryString nvarchar(4000))
GO

INSERT INTO QueryStringTest
SELECT
'Code=web.co.uk%product_250p&TranID=14078800&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077 770&OtherFlag0=services.web.stats.PostContentDeliv ery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'

SELECT
SUBSTRING(QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString))+7,
CHARINDEX('&',
QueryString,
CHARINDEX(
'TRANID=',
UPPER(QueryString)
)
)-CHARINDEX(
'TRANID=',
UPPER(QueryString)
)-7
)
AS TransID
FROM QueryStringTest ORDER BY TransID
DROP TABLE QueryStringTest

You could also look into regular expressions.

Mar 20 '06 #4

P: n/a
That worked perfectly.
Much appreciated.

Mar 20 '06 #5

P: n/a
I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example

OtherID=1638256785230&TranID=12345

How do I edit the script to capture these records?

Regards,
Ciarán

Mar 20 '06 #6

P: n/a
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&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_free&TranID=14077576&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077583&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077584&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_150p&TranID=14077579&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077603&Other Flag0=services.web.stats.PostCSDelivery&OtherFlag0 par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077741&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077757&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=deliv'
UNION SELECT
'Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077 770&OtherFlag0=services.web.stats.PostContentDeliv ery&OtherFlag0par=deliv'
UNION SELECT
'Code=web.co.uk%product_250p&TranID=14077604&Other Flag0=services.web.stats.PostContentDelivery&Other Flag0par=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

Mar 20 '06 #7

P: n/a
(ch********@hotmail.com) writes:
I've just encountered a little problam.
Contrary to the criteria I provided earlier there are cells which end
with the TranID, like for example

OtherID=1638256785230&TranID=12345

How do I edit the script to capture these records?


Here is a query, a little different from figitals:

SELECT convert(int, str2)
FROM (SELECT str2 =
CASE WHEN str1 LIKE '%[^0-9]%'
THEN substring(str1, 1, patindex('%[^0-9]%', str1) - 1)
ELSE str1
END
FROM (SELECT str1 = substring(str,
charindex('TranID=', str) + len('TranId='),
len(str))
FROM QueryStringTest) AS a) AS b

By using nested derived tables, it is possibly easier to see the
solution step for step. Or it's more confusing. :-)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Mar 20 '06 #8

P: n/a
Perfect!
Thanks

Mar 21 '06 #9

This discussion thread is closed

Replies have been disabled for this discussion.