SQL group by part of string

Question

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.

You should read that question: http://stackoverflow.com/questions/8726111/sql-server-find-nth-occurrence-in-a-string — MarcinJuraszek, Aug 21 '13 at 09:45
Is this one, two or three slashes: `pl/XXX`? I ask because it might be the same path as `/pl/XXX/`. — Tim Schmelter, Aug 21 '13 at 09:54
@TimSchmelter all data starts with slash so it is not a problem — Piotr Stapp, Aug 21 '13 at 10:32

score 10 · Accepted Answer · answered Aug 21 '13 at 09:47

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.

Sergey Kalinichenko · Answer 2 · 2013-08-21T10:16:43.293

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.

T I · Answer 3 · 2013-08-21T11:53:52.220

You could find the position of each position of / and trim to the MAX(position) - presuming the third / is the last / as per the data.

DECLARE @tbl TABLE ( u VARCHAR(255), t INT, d DATE)

INSERT INTO @tbl (u, t, d) VALUES
('/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')

;WITH split AS (
    SELECT u, 1 s, CHARINDEX('/', u) p
    FROM @tbl
    UNION ALL
    SELECT u, p + 1, CHARINDEX('/', u, p + 1)
    FROM split
)

SELECT LEFT(t.u, split.i), MAX(t.t), MAX(t.d)
FROM @tbl t
JOIN (
    SELECT u, MAX(p) i 
    FROM split
    GROUP BY u
) split ON split.u = t.u
GROUP BY LEFT(t.u, split.i)

With a slight adjustment to the cte you can control the occurences

DECLARE @n INT = 3    -- 'nth occurence'

;WITH split AS (
    SELECT u, CHARINDEX('/', u) i, 1 r
    FROM (
        SELECT DISTINCT u
        FROM @tbl
    ) t
    WHERE CHARINDEX('/', u) > 0
    UNION ALL
    SELECT u, CHARINDEX('/', u, i + 1), r + 1
    FROM split
    WHERE r < @n
    AND CHARINDEX('/', u, i + 1) > 0
)

SELECT LEFT(t.u, split.i) u, MAX(t.t) t , MAX(t.d) d
FROM @tbl t
JOIN split ON split.u = t.u
GROUP BY LEFT(t.u, split.i)

sql fiddle demo

SQL group by part of string

3 Answers3