Help with a substring query

[chudson007]

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

[Robert Klemme]

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

[Sergey]

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

[figital]

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.

[chudson007]

That worked perfectly.
Much appreciated.

[chudson007]

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

[figital]

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

[Erland Sommarskog]

(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

[chudson007]

Perfect!
Thanks