| Author |
 Topic  |
|
|
obewankanoochie
Starting Member
24 Posts |
 Posted - 09/26/2007 : 18:17:26
|
I've got a table with a list of directories. I need to output all the directories that are at least 3 directories deep. Here's an example of the entries:
1) Sports
2) Sports/Football
3) Sports/Football/American
4) Sports/Football/American/College_and_University
5) Sports/Football/American/College_and_University/NCAA_Division_III
6) Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference
7) Sports/Football/American/College_and_University/NCAA_Division_III/Atlantic_Central_Football_Conference/Frostburg_State
8) Sports/Darts
9) Sports/Darts/Organizations
10) Sports/Darts/Organizations/United_States
11) Sports/Darts/Organizations/United_States/Alabama
I need to run a query that only results in 4,5,6,7,10,11, or anything with at least 3 slashes "/" in it.
Please tell me that this is possible. I'm out of ideas. Thanks.
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 09/26/2007 : 19:16:46
|
SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3
KH
Time is always against us
|
 |
|
|
dinakar
Flowing Fount of Yak Knowledge
USA
2507 Posts |
Posted - 09/26/2007 : 19:44:33
|
quote:
Originally posted by khtan
SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3
KH
Time is always against us
Hey thats smart 
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/ |
|
|
|
obewankanoochie
Starting Member
24 Posts |
Posted - 09/26/2007 : 20:20:12
|
quote:
Originally posted by dinakar
quote:
Originally posted by khtan
SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3
KH
Time is always against us
Hey thats smart
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
Very clever. Thank you!
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/27/2007 : 02:58:04
|
What about this?SELECT Directory
FROM SampleTable
WHERE Directory LIKE '%/%/%/%'
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/27/2007 : 02:59:37
|
And if you want it "dynamic"...SELECT ID,
data
FROM @Sample
WHERE data LIKE '%' + REPLICATE('/%', 3)
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16769 Posts |
Posted - 09/27/2007 : 04:22:54
|
quote:
Originally posted by Peso
What about this?SELECT Directory
FROM SampleTable
WHERE Directory LIKE '%/%/%/%'
E 12°55'05.25"
N 56°04'39.16"
That's an smarter solution !
KH
Time is always against us
|
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/27/2007 : 04:30:01
|
Thank you!
I thought this is a better solution because it also works on TEXT datatype columns.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/27/2007 : 05:35:38
|
"it also works on TEXT datatype columns"
Blimey! Some hefty hierarchy in your organisation! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
Sweden
29156 Posts |
Posted - 09/27/2007 : 06:33:16
|
Not so many levels rather than lengthy "catchy" names.
E 12°55'05.25"
N 56°04'39.16" |
|
|
|
Kristen
Test
United Kingdom
22191 Posts |
Posted - 09/27/2007 : 06:36:37
|
"lengthy "catchy" names"
Ah yes! The Antidisestablishmentarianism department ... |
|
|
| |
Topic |
|
Is this possible?
