R: sql -- scan for @ in field

Maybe this can help you on how LOCATE can be used ...

We have a db field called REQUEST that contains an Xml statement.

Between tags <ApplGroup> and </ApplGroup> there is the application group name.

We want to count the activity grouped by application.

This is the SQL statement:

SELECT cast(substr(REQUEST,
cast(locate('<ApplGroup>', REQUEST, 1) as smallint) + 11,
cast(locate('</ApplGroup>', REQUEST, 3) as smallint) - 11 -
cast(locate('<ApplGroup>', REQUEST, 1) as smallint)
) as
char(20)) Application , count(*) Usage
from wpodtrpf where datelog = '2008-07-31'
and locate('<ApplGroup>', REQUEST, 1) > 0
group by
cast(substr(RICHIESTA,
cast(locate('<ApplGroup>', REQUEST, 1) as smallint) + 11,
cast(locate('</ApplGroup>', REQUEST, 3) as smallint) - 11 -
cast(locate('<ApplGroup>', REQUEST, 1) as smallint)
) as
char(20))
order by 2 desc

----------------
Giuseppe.


--- Gio 13/11/08, Charles Sallee <csallee@xxxxxxxxx> ha scritto:

Da: Charles Sallee <csallee@xxxxxxxxx>
Oggetto: sql -- scan for @ in field
A: RPG400-L@xxxxxxxxxxxx
Data: Giovedì 13 novembre 2008, 04:03
Mr. West may not need help with SQL, but I do!!

I have looked and looked for a scan function in SQL and I
can not find
it. Can anyone point me to a function I can use in SQL to
find the @ in
an email address so I can split the email address into two
fields?

Thanks, Chuck

Chuck Sallee
CIS Manager
859-425-2242
Lexington Division of Police
csallee@xxxxxxxxx


--
This is the RPG programming on the AS400 / iSeries
(RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

Unisciti alla community di Io fotografo e video, il nuovo corso di fotografia di Gazzetta dello sport:
http://www.flickr.com/groups/iofotografoevideo