SQL and Regex | check url path

Question

I'm trying to show in SQL only the URL that have a path, for example:

stackoverflow.com/ - no path

stackoverflow.com/abc.html - no path

stackoverflow.com/abc- path

stackoverflow.com/abc/bla/- path

now in regex the answer is \/\w+$

I know regex is not working well with SQL, so i don't know how could i check URL.

this is what i tried:

select * from sites where url like '\/\w+$'

Your assumptions are wrong; stackoverflow.com/abc is not a path as abc is a file. — Salman Arshad
– Salman Arshad, Commented Feb 11, 2018 at 11:42

Salman Arshad · Accepted Answer · 2018-02-11 11:45:24Z

1

This checks if URL contains two slashes:

SELECT *
FROM testdata
WHERE url LIKE '%/%/%'

Technically speaking, the URLs stackoverflow.com/abc.html and stackoverflow.com/abc are identical, where you have a hostname and a filename. The above example will only match stackoverflow.com/abc/bla/.

edited Feb 11, 2018 at 11:45

answered Feb 11, 2018 at 11:40

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lukasz Szozda · Accepted Answer · 2018-02-11 10:30:54Z

0

You could use CHARINDEX:

WITH cte(p) AS (
    SELECT 'stackoverflow.com/' 
    UNION SELECT 'stackoverflow.com/abc.html' 
    UNION SELECT 'stackoverflow.com/abc'
    UNION SELECT 'stackoverflow.com/abc/bla'
)
SELECT *
FROM cte
WHERE CHARINDEX('/', REVERSE(p)) < CHARINDEX('.', REVERSE(p));

Rextester Demo

edited Feb 11, 2018 at 10:30

answered Feb 11, 2018 at 10:25

Lukasz Szozda

181k26 gold badges278 silver badges326 bronze badges

Collectives™ on Stack Overflow

SQL and Regex | check url path

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related