Clever way to get the records you want with certain number of a given character

<< Insert binary data like images into SQL Server without front-end application | Home | SQL Server 2005 too smart? >>

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)

Feedback

# 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 '%/%/%'

9/27/2007 6:12 PM | Sharon Matyk

# 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.

9/28/2007 4:46 PM | Peter Larsson

# 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...

9/29/2007 3:31 PM | doco

# 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...

9/29/2007 8:47 PM | Peso

# 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

8/9/2010 9:44 AM | dineshrajan

Comments have been closed on this topic.

Thinking outside the box

My Links

Advertisement

News

Archives

Post Categories