Group newbie: question on parsing a field value

I have a text field (called TreeNode) that contains node identifiers for a
dynamic tree on a web page. For example:

"1.1"
"1.2"
"1.2.1"
"1.2.2"
--
--
'1.2.10"
--
etc..

What I need to do is compare this field "value" to another value in a query.
(I'm using ASP and VBScript to create the statement). For example:

sql = "SELECT SomeField FROM MyTable WHERE TreeNode>=' " & MyNode & " ' "

The problem I run into is when the TreeNode value is say "1.2.10", and it's
being compared against "1.2.1" and "1.2.2". It should be greater than both
of these (in my implementation of this), but it actually falls between the
two, since it is a text comparison.

All I'm really interested in is the last value. But I can't use the RIGHT
function, because the last value may be one or more digits, and there could
be any number of levels (periods).

Is there an SQL function I could use that would strip away everything but
the text following the last period? I could then easily do the same in the
ASP script and compare integers. Something like:

sql = "SELECT SomeField FROM MyTable WHERE GetLastValueSQL(TreeNode) >= " &
GetLastValueASP(MyTreeNode)

This also needs to work in MS Access BTW :-)

Thanks in advance for any help!

Calan

AxMaster Guitar Software
www.jcsautomation.com
www.jcsautomation.com/music.asp
Music software and web design/hosting

"Reality exists only in the minds of the extremely deranged""Calan" <calan_svcREMOVE@.yaNOSPAMhoo.com> wrote in message news:<P3Jcc.643$FB1.182@.fe25.usenetserver.com>...
> I have a text field (called TreeNode) that contains node identifiers for a
> dynamic tree on a web page. For example:
> "1.1"
> "1.2"
> "1.2.1"
> "1.2.2"
> --
> --
> '1.2.10"
> --
> etc..
> What I need to do is compare this field "value" to another value in a query.
> (I'm using ASP and VBScript to create the statement). For example:
> sql = "SELECT SomeField FROM MyTable WHERE TreeNode>=' " & MyNode & " ' "
> The problem I run into is when the TreeNode value is say "1.2.10", and it's
> being compared against "1.2.1" and "1.2.2". It should be greater than both
> of these (in my implementation of this), but it actually falls between the
> two, since it is a text comparison.
> All I'm really interested in is the last value. But I can't use the RIGHT
> function, because the last value may be one or more digits, and there could
> be any number of levels (periods).
> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
> sql = "SELECT SomeField FROM MyTable WHERE GetLastValueSQL(TreeNode) >= " &
> GetLastValueASP(MyTreeNode)
> This also needs to work in MS Access BTW :-)
> Thanks in advance for any help!
> Calan
> AxMaster Guitar Software
> www.jcsautomation.com
> www.jcsautomation.com/music.asp
> Music software and web design/hosting
> "Reality exists only in the minds of the extremely deranged"

This is one way:

declare @.node varchar(50)
set @.node = '1.2.1.10'

select substring(
@.node,
len(@.node) - charindex('.', reverse(@.node))+2,
charindex('.', reverse(@.node))
)

Or this may be easier to read:

select reverse(left(reverse(@.node), charindex('.', reverse(@.node))-1))

You could put this into a function (in SQL2000), but it would be
invoked once per row in queries, so using a stored procedure is
probably a better approach.

Simon|||> Is there an SQL function I could use that would strip away everything but
> the text following the last period? I could then easily do the same in the
> ASP script and compare integers. Something like:
> This also needs to work in MS Access BTW :-)

Hi,

I don't know anything about Access. You can use a table of numbers
trick to parse your "node" into individual nodes by using the period
as a delimiter. Erland has documented it in his site. In the code
below, I called my table of numbers TALLY which has one column ID with
values from 1,2,3,... to 8000.

declare @.node varchar (50)
set @.node='1.2.10'

SELECT
substring(phrase,s,(e-s-1)) as NODES
FROM
(
SELECT
id,
phrase,
charindex('.','.'+phrase+'.',id) as s,
charindex('.','.'+phrase+'.',id+1) as e
FROM tally,(select phrase=@.node) A
WHERE charindex('.','.'+phrase+'.',id) <
charindex('.','.'+phrase+'.',id+1)
) B

OUTPUTS:
NODES
----------------
1
2
10

Further modifying it to output the last NODE piece:
SELECT
substring(phrase,s,(e-s-1)) as NODES, identity(int,1,1) as i
INTO #T
FROM
(
SELECT
id,
phrase,
charindex('.','.'+phrase+'.',id) as s,
charindex('.','.'+phrase+'.',id+1) as e
FROM tally,(select phrase=@.node) A
WHERE charindex('.','.'+phrase+'.',id) <
charindex('.','.'+phrase+'.',id+1)
) B

SELECT NODES FROM #T WHERE i=(SELECT max(i) as i FROM #T)

OUTPUTS:
NODES
----------------
10