Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    18

    Unanswered: substr and instr help (simple?)

    I have a table like this:

    URL (varchar2)
    CNN.com?id=1
    CNN.com?no=2
    CNN.com
    msnbc.com

    I'm trying get a count of URLs, ignoring everything after the "?"

    SELECT SUBSTR(URL,1,(INSTR(URL,'?',1,1)-1)), COUNT(1) AS VISITCOUNT
    FROM SESSIONDATA
    GROUP BY SUBSTR(URL,1,(INSTR(URL,'?',1,1)-1));

    this will only return rows of 'www.cnn.com/' that actually have a question mark in them. how do i get it to include all instances?

    given the table above, the return should look like this:
    url visitcount
    cnn.com 3
    msnbc.com 1

    hope this makes sense...
    thanks folks!

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Code:
    SQL> select * from test;
    
    URL
    --------------------
    cnn.com?id=1
    cnn.com?no=2
    cnn.com
    msnbc.com
    
    SQL> select substr(url, 1, instr(url, '.com') + 3) url,
      2         count(*) visitcount
      3  from test
      4  group by substr(url, 1, instr(url, '.com') + 3);
    
    URL                  VISITCOUNT
    -------------------- ----------
    cnn.com                       3
    msnbc.com                     1
    
    SQL>

  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Alternatively
    Code:
    SELECT plain_url,
           count(*) AS visitcount
    FROM (
        SELECT CASE 
                 WHEN instr(url,'?',1,1) > 0 then substr(url,1,(instr(url,'?',1,1)-1))
                 ELSE url
               END as plain_url
        FROM sessiondata
    ) t
    GROUP BY plain_url

  4. #4
    Join Date
    Jun 2010
    Posts
    18
    once again, the people on this forum come through... thanks folks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •