Is this possible? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Is this possible?

Author

Topic

obewankanoochie
Starting Member

24 Posts

Posted - 2007-09-26 : 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)

17689 Posts

Posted - 2007-09-26 : 19:16:46

[code]SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3[/code]

KH
[spoiler]Time is always against us[/spoiler]

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-09-26 : 19:44:33

quote:
Originally posted by khtan

SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3

KH
[spoiler]Time is always against us[/spoiler]

Hey thats smart

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

obewankanoochie
Starting Member

24 Posts

Posted - 2007-09-26 : 20:20:12

quote:
Originally posted by dinakar

quote:
Originally posted by khtan

SELECT *
FROM sampletable
WHERE LEN(directory) - LEN(REPLACE(directory, '/', '')) >= 3

KH
[spoiler]Time is always against us[/spoiler]

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

30421 Posts

Posted - 2007-09-27 : 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

30421 Posts

Posted - 2007-09-27 : 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)

17689 Posts

Posted - 2007-09-27 : 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
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 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

22859 Posts

Posted - 2007-09-27 : 05:35:38

"it also works on TEXT datatype columns"

Blimey! Some hefty hierarchy in your organisation!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-27 : 06:33:16

Not so many levels rather than lengthy "catchy" names.

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-27 : 06:36:37

"lengthy "catchy" names"

Ah yes! The Antidisestablishmentarianism department ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources