Help with a substring query

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&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Regards,
Ciarnchudson007@.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&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_free&TranID=14077576&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077583&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077584&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_150p&TranID=14077579&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077603&OtherFlag0=services.web.stats.PostCSDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077741&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077757&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%2Fpush_wallpaper_250p&TranID=14077770&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv
> Code=web.co.uk%product_250p&TranID=14077604&OtherFlag0=services.web.stats.PostContentDelivery&OtherFlag0par=deliv

Hint: use LIKE.

Kind regards

robert|||--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=14077576&OtherFlag0=services.web.stats.P*ostCSDelivery&OtherFlag0par=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|||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=1638256785230&TranID=12345

How do I edit the script to capture these records?

Regards,
Ciarn|||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|||(chudson007@.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, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perfect!
Thanks

Help w/aggregate function in Matrix

On a report we have a matrix. The data cell has the following expression:
=iif(Fields!Score.Value=0, "", Fields!Score.Value)
When we run the report, we get the following warning:
The value expression for the textbox 'Score' references a field
outside an aggregate function. Value expressions in matrix cells should be
aggregates, to allow for subtotaling.
What does this mean and how can I resolve it?
The goal it to suppress the display of zero (0). We have tried setting the
format of the data cell to be "#", but the zero is still displayed. So we
have been using expressions like the above to achieve this.
The user creating this report is using the stand-alone C# IDE with Reporting
Services. This warning prevents them from previewing the report. Another
user using VS.NET 2003 is able to preview the report despite the warning.
The report renders on our test reporting server. If we need to just ignore
the warning, how can we get the user using C# to be able to preview the
report?
Thanks,
ChrisMatrix cells are always in the scope of two groupings and you could have
multiple data rows which match the group instance values. Therefore, you
should always use aggregate functions when referencing fields in a matrix
cell (hence, a processing warning gets generated).
If you don't use an explicit aggregate function in the matrix cell, we would
implicitly use the first row's field value. I believe you actually don't
want just the first value, but rather the sum - so you should change the
expression to:
=iif(Sum(Fields!Score.Value)=0, "", Sum(Fields!Score.Value))
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
In your case, I believe you want
"Chris Walls" <chwalls@.community.nospam> wrote in message
news:OVqzX42OFHA.1500@.TK2MSFTNGP09.phx.gbl...
> On a report we have a matrix. The data cell has the following expression:
>
> =iif(Fields!Score.Value=0, "", Fields!Score.Value)
>
> When we run the report, we get the following warning:
>
> The value expression for the textbox 'Score' references a field
> outside an aggregate function. Value expressions in matrix cells should
> be aggregates, to allow for subtotaling.
>
>
> What does this mean and how can I resolve it?
>
>
> The goal it to suppress the display of zero (0). We have tried setting
> the format of the data cell to be "#", but the zero is still displayed.
> So we have been using expressions like the above to achieve this.
>
>
> The user creating this report is using the stand-alone C# IDE with
> Reporting Services. This warning prevents them from previewing the
> report. Another user using VS.NET 2003 is able to preview the report
> despite the warning. The report renders on our test reporting server. If
> we need to just ignore the warning, how can we get the user using C# to be
> able to preview the report?
>
>
> Thanks,
> Chris
>
>