Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL group by part of string

Tags:

sql

sql-server

I have following data in my table:

URL         TIME    DATE
--------------------------------------
/x          11      2013-08-01
/x          11      2013-08-01
/pl/        11      2013-08-01
/pl/        11      2013-08-03
/pl/XXX/    11      2013-08-01
/pl/XXX/    11      2013-08-04
/pl/XXX/1   11      2013-08-01
/pl/XXX/2   11      2013-08-01
/pl/YYY/    11      2013-08-01
/pl/YYY/1   11      2013-08-01
/pl/YYY/2   11      2013-08-04
/pl/YYY/3   11      2013-08-04

Is there a way to group by URL up to third slash (/) in SQL Server? Unfortunately there exists record which contains less than three.

like image 996
Piotr Stapp Avatar asked Aug 21 '13 09:08

Piotr Stapp


People also ask

How do I SELECT part of a string in SQL?

The SUBSTRING() function extracts some characters from a string.

Can we use substring in GROUP BY clause?

Yes I agree with you. But if substr(country,0,20) is same for 2 country , but the actual value of country is different, in that case, we can't display both country value in a group by output.

Can you GROUP BY 2 things in SQL?

We can group the resultset in SQL on multiple column values. When we define the grouping criteria on more than one column, all the records having the same value for the columns defined in the group by clause are collectively represented using a single record in the query output.

Can you GROUP BY coalesce?

With GROUP BY coalesce(b,a) the tuples (null,1) , (1,1) , (2,1) and (17,1) would end up in the same group. If you want the "conditional" grouping, then yes, the version with coalesce is probably what you want.


2 Answers

One trick to count the number of slashes in a string is:

len(url) - len(replace(url,'/',''))

You can then use charindex three times to find the position of the third slash:

select  BeforeThirdSlash
,       max([date])
from    (
        select  case 
                when len(url) - len(replace(url,'/','')) < 3 then url
                else substring(url, 1, charindex('/', url, charindex('/', 
                         url, charindex('/', url)+1)+1)-1)
                end as BeforeThirdSlash
        ,       *
        from    @t
        ) as SubQueryAlias
group by
        BeforeThirdSlash

Live example at SQL Fiddle.

like image 184
Andomar Avatar answered Oct 18 '22 21:10

Andomar


A simple expression that lets you grab the substring up to the third '/' character is as follows:

case
    when patindex('%/%/%/%', url) = 0 then url
    else left(url,charindex('/',url,charindex('/',url,charindex('/',url)+1)+1))
end

The patindex checks that there are at least three slashes; the left extracts the substring up to and including the third one.

With this expression in hand, writing a group by is simple:

SELECT
    url3, max(tm), max(dt)
FROM (
    SELECT
        CASE
            WHEN patindex('%/%/%/%', url) = 0 THEN url
            ELSE left(url,charindex('/',url,charindex('/',url,charindex('/',url)+1)+1))
        END AS url3
    ,   tm
    ,   dt
    FROM test
) x
GROUP BY url3

Demo on SqlFiddle.

like image 32
Sergey Kalinichenko Avatar answered Oct 18 '22 20:10

Sergey Kalinichenko