R: sql -- scan for @ in field

[beppecosta <beppecosta@xxxxxxxxxxx>]

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