SELECT on string field

[Arcangelo Casavola]

Hi,
I've a field with
www.s.com/p.php?id=3
www.s.com/p.php?id=4
www.s.com/p.php?id=5
if a do a query with the atribute 'group by' i want that the system regroup 
all that is after the '?'
is that possible?

SELECT field WHERE all that is after the '?' GROUP BY all that is after the 
'?'
the result should be:

www.s.com/p.php
thanks
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: SELECT on string field

[Jay Blanchard]

[snip]
I've a field with

www.s.com/p.php?id=3
www.s.com/p.php?id=4
www.s.com/p.php?id=5


if a do a query with the atribute 'group by' i want that the system
regroup 
all that is after the '?'
is that possible?

SELECT field WHERE all that is after the '?' GROUP BY all that is after
the 
'?'
the result should be:

www.s.com/p.php
[/snip]

SELECT substring(field, 1, 15) FROM table WHERE substring(field, 16) =
'?' GROUP BY field
should return what you have asked for above (www.s.com/p.php). However,
if you group by each thing that is after the '?' your result set would
be

www.s.com/p.php (this group is id=3)
www.s.com/p.php (this group is id=4)
www.s.com/p.php (this group is id=5)

You can also try

SELECT substring(field, 1, locate('?', field)-1) FROM table GROUP BY
substring(field, locate('?', field));

http://dev.mysql.com/doc/mysql/en/string-comparison-functions.html
http://dev.mysql.com/doc/mysql/en/string-functions.html

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: SELECT on string field

[Jose Miguel Pérez]

Hi Arcangelo!

Try this:

SELECT COUNT(*), url, SUBSTRING_INDEX(url, ?, 1) AS page_name
FROM sites
GROUP BY page_name

SUBSTRING_INDEX will return the leftmost (or rightmost) part of a string
before the ocurrence of a delimiter (? in this case. See Documentation:
Chapter String Functions). The trick here is to GROUP BY the result of
this operation.

Note that you could elaborate this further so that you could GROUP by
the host name. You will need to anidate SUBSTRING_INDEX calls so that you
end up with the desired data.

For example:

SELECT COUNT(*), url, SUBSTRING_INDEX(SUBSTRING_INDEX(url, ?, 1),
/, 1) AS host_name
FROM sites
GROUP BY host_name

This will group together the following URIs as one:

- www.domain.com/pageone.php(There is no ?)
- www.domain.com?id=2   (There is no /)


Beware though, this usage will not use indexes on url column so be
careful and do not use this on big tables. You will need to use more
elaborated techniques to speed up query times on big tables.

Cheers,
Jose Miguel.



- ORIGINAL MESSAGE ---
 I've a field with

 www.s.com/p.php?id=3
 www.s.com/p.php?id=4
 www.s.com/p.php?id=5


 if a do a query with the atribute 'group by' i want that the system
regroup
 all that is after the '?'
 is that possible?

 SELECT field WHERE all that is after the '?' GROUP BY all that is after
the
 '?'
 the result should be:

 www.s.com/p.php


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]