Help with a t-sql query

Question

Hi

Based on the following table

Path
----------------------
area1
area1\area2
area1\area2\area3
area1\area2\area3\area4
area1\area2\area5
area1\area2\area6
area1\area7

Input to my stored procedure is areapath and no.of children (indicates the depth that needs to considered from the input areapath)

areapath=area1
children=2

Above should give

Path
-----------
area1
area1\area2
area1\area2\area3
area1\area2\area5
area1\area2\area6
area1\area7

similary for
areapath=area2 and children=1 output should be

Path
---------------
area1\area2
area1\area2\area3
area1\area2\area5
area1\area2\area6

I am confused how to write a query for this one.

Answer 1

Hmm... It seems like this is the part where you learn to start normalizing your data. It'd be trivial if you'd simply stored your tree as a tree.

That being said, you can probably pull this off with some fancy string splitting and parsing.

You'll need a UDF that splits a line containing slashes into an array of tokens. TSQL has neither a built-in function to do this nor the concept of arrays, so you'll need to fudge a bit and use temp tables.

Then it's simply a matter of comparing the [children]th entry in that array with the supplied [areapath] in your WHERE clause.

Answer 2

I agree with Jason, but remember you can use VB.NET/C# to write the splitting function and then bring it in to SQL Server through the CLR import ability. This of course assumes you have the proper authorizations on the server to do it. But while SQL is wonderful for many things, it does not have as robust string manipulation tools as other languages do.

Answer 3

I guess it is not a homework.

create table #Tt([path] varchar(255))
insert #tt values( 'area1')
insert #tt values( 'area1\area2')
insert #tt values( 'area1\area2\area3')
insert #tt values( 'area1\area2\area3\area4')
insert #tt values( 'area1\area2\area5')
insert #tt values( 'area1\area2\area6')
insert #tt values( 'area1\area7')

select * from  #tt where len([path])-len(replace([path],'\','')) = 2 and [path] like 'area1\%'

drop table #tt