Clever way to get the records you want with certain number of a given character
Consider this test data
CREATE TABLE #Temp (ID INT, Directory TEXT)
INSERT #Temp
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
SELECT ID,
Directory
FROM #Temp
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?
SELECT ID,
Directory
FROM #Temp
WHERE Directory LIKE '%' + REPLICATE('/%', 2)
Legacy Comments
|
Sharon Matyk
2007-09-27 |
re: Clever way to get the records you want with certain number of a given character what's wrong with: select * from #Temp where Directory like '%/%/%' |
|
Peter Larsson
2007-09-28 |
re: Clever way to get the records you want with certain number of a given character Nothing wrong with that, more than it is hard-wired. If you use my suggestion, you can even put the query in a stored procedure (or function) and call it with a parameter telling how many / you want. |
|
doco
2007-09-29 |
re: Clever way to get the records you want with certain number of a given character You can his way too. With a looping mech that adds '%/' an many times as is passed in args... |
|
Peso
2007-09-29 |
re: Clever way to get the records you want with certain number of a given character Yes you can! And the code will be easier to maintain than the REPLICATE approach too... |
|
dineshrajan
2010-08-09 |
re: Clever way to get the records you want with certain number of a given character Hi another approach select * from #Temp t where PATINDEX('%[/]%[/]%',Directory)> 0 |