Consider this test data
CREATE TABLE #Temp (ID INT, Directory TEXT)
SELECT 1, 'Sports' UNION ALL
SELECT 2, 'Sports/Football' UNION ALL
SELECT 3, 'Sports/Football/American' UNION ALL
SELECT 4, 'Sports/Football/American/College_and_University' UNION ALL
SELECT 5, 'Sports/Football/American/College_and_University/NCAA_Division_III' UNION ALL
SELECT 6, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference' UNION ALL
SELECT 7, 'Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State' UNION ALL
SELECT 8, 'Sports/Darts' UNION ALL
SELECT 9, 'Sports/Darts/Organizations' UNION ALL
SELECT 10, 'Sports/Darts/Organizations/United_States' UNION ALL
SELECT 11, 'Sports/Darts/Organizations/United_States/Alabama'
Say you want to return all records that are at least three directories down, ie having at least 2 dividers.
This is the most common way I have encountered
WHERE LEN(Directory) - LEN(REPLACE(Directory, '/', '')) >= 2
This works but have one drawback. It does not work on TEXT columns!
This is what I came up with today in this topic Is this possible?
WHERE Directory LIKE '%' + REPLICATE('/%', 2)