Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE urls ( url varchar(255) )
insert into urls (url) values('www.abc.com/product/')
1 rows affected

insert into urls (url) values('www.abc.com/product/coffee/')
1 rows affected

insert into urls (url) values('www.abc.com/product/coffee/caffee_name')
1 rows affected

select url, (LENGTH(url) - LENGTH (REPLACE(url, '/', ''))) from urls where url like 'www.abc.com/product/%'
AND (LENGTH(url) - LENGTH (REPLACE(url, '/', ''))) BETWEEN 3 AND 4


URL
(LENGTH(URL)-LENGTH(REPLACE(URL,'/','')))


www.abc.com/product/coffee/
3

www.abc.com/product/coffee/caffee_name
3


   SELECT
       url
     , REGEXP_COUNT(url, '/') 
    FROM urls
    WHERE REGEXP_COUNT(url, '/') BETWEEN 3 AND 4


URL
REGEXP_COUNT(URL,'/')


www.abc.com/product/coffee/
3

www.abc.com/product/coffee/caffee_name
3

URL	(LENGTH(URL)-LENGTH(REPLACE(URL,'/','')))
www.abc.com/product/coffee/	3
www.abc.com/product/coffee/caffee_name	3