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=3D' 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=3Dweb.co.uk%product_250p&TranID=3D14078800&OtherFlag0=3Dservices.web.s=
tats.PostContentDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_free&TranID=3D14077576&OtherFlag0=3Dservices.web.s=
tats.PostCSDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077583&OtherFlag0=3Dservices.web.s=
tats.PostCSDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077584&OtherFlag0=3Dservices.web.s=
tats.PostCSDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_150p&TranID=3D14077579&OtherFlag0=3Dservices.web.s=
tats.PostCSDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077603&OtherFlag0=3Dservices.web.s=
tats.PostCSDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077741&OtherFlag0=3Dservices.web.s=
tats.PostContentDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077757&OtherFlag0=3Dservices.web.s=
tats.PostContentDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%2Fpush_wallpaper_250p&TranID=3D14077770&OtherFlag0=3Dservi=
ces.web.stats.PostContentDelivery&OtherFlag0par=3Ddeliv
Code=3Dweb.co.uk%product_250p&TranID=3D14077604&OtherFlag0=3Dservices.web.s=
tats.PostContentDelivery&OtherFlag0par=3Ddeliv


Regards,
Ciar=E1n

--something like this:
declare @tranid_position int, @amp_position int,@string varchar(8000),
@rest_of_string varchar(8000), @Result_string varchar(8000)
set
@string=3D'Code=3Dweb.co.uk%product_free&TranID=3D14077576&OtherFlag0=3Dser=
vices.web.stats.P=ADostCSDelivery&OtherFlag0par=3Ddeliv
'
set @tranid_position=3Dcharindex('&TranID=3D',@string)
set @rest_of_string=3D substring(@string,@tranid_position+8,8000)
set @amp_position=3Dcharindex('&',@rest_of_string)
set @Result_string=3Dleft(@rest_of_string,@amp_position-1)
select @Result_string

Here you go...

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'

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.

That worked perfectly.
Much appreciated.

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=3D1638256785230&TranID=3D12345

How do I edit the script to capture these records?

Regards,
Ciar=E1n

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

[quoted text, click to view]

Hint: use LIKE.

Kind regards

(chudson007@hotmail.com) writes:
[quoted text, click to view]

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, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at

Perfect!
Thanks