Thinking outside the box

Patron Saint of Lost Yaks
posts - 203, comments - 734, trackbacks - 4

My Links

Advertisement

News

Archives

Post Categories

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)

Print | posted on Thursday, September 27, 2007 10:41 AM | Filed Under [ Algorithms ]

Feedback

Gravatar

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

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

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

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

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

Powered by:
Powered By Subtext Powered By ASP.NET