(Sorry about the vague title, but I couldn't think of a title that
even remotely fit this situation.)

Consider a table with single column, consisting of URL paths in a
VARCHAR field, like this:

/animals/domesticated/cats
/animals/domesticated/dogs

However, say a directory can be "wildcarded" with a star, like this:

/animals/*/cats
/animals/*/dogs

If we select all four of the above URLs in a single statement, I want
to order them **as if the directories were in different cells and I
ordered ascending from the first cell, down to the last.**

So I want those URLs back like this --

/animals/*/cats
/animals/domesticated/cats
/animals/*/dogs
/animals/domesticated/dogs

-- since "*" (star) comes before the "d" in "domesticated." Put
another way, I want SQL to evaluate the second directory in the string
as a single value bound by the slashes ("*" or "domesticated") rather
than a collection of characters.

Effectively, I want to replicate the result that would occur if I had
the data organized into different cells like this --

field1 | field2 | field 3
----------------------------------
animals | * | cats
animals | * | dogs
animals | domesticated | cats
animals | domesticated | dogs

-- then had this on my SQL: "ORDER BY field1, field2, field3".

Possible?

Deane wrote:
> (Sorry about the vague title, but I couldn't think of a title that
> even remotely fit this situation.)
>
> Consider a table with single column, consisting of URL paths in a
> VARCHAR field, like this:
>
> /animals/domesticated/cats
> /animals/domesticated/dogs
>
> However, say a directory can be "wildcarded" with a star, like this:
>
> /animals/*/cats
> /animals/*/dogs
>
> If we select all four of the above URLs in a single statement, I want
> to order them **as if the directories were in different cells and I
> ordered ascending from the first cell, down to the last.**
>
> So I want those URLs back like this --
>
> /animals/*/cats
> /animals/domesticated/cats
> /animals/*/dogs
> /animals/domesticated/dogs
>
> -- since "*" (star) comes before the "d" in "domesticated." Put
> another way, I want SQL to evaluate the second directory in the string
> as a single value bound by the slashes ("*" or "domesticated") rather
> than a collection of characters.
>
> Effectively, I want to replicate the result that would occur if I had
> the data organized into different cells like this --
>
> field1 | field2 | field 3
> ----------------------------------
> animals | * | cats
> animals | * | dogs
> animals | domesticated | cats
> animals | domesticated | dogs
>
> -- then had this on my SQL: "ORDER BY field1, field2, field3".
>
> Possible?

select field1, field2, field3 from table where field1 = 'animals' and
field3 in ('cats','dogs');

or if it is a single field as in your initial query

where field1 like '/animals/%/cats' or field1 like '/animals/%/dogs'

or more broadly:

where field1 like '%/cats' or field1 like '%/dogs'

Deane wrote:
> (Sorry about the vague title, but I couldn't think of a title that
> even remotely fit this situation.)
>
> Consider a table with single column, consisting of URL paths in a
> VARCHAR field, like this:
>
> /animals/domesticated/cats
> /animals/domesticated/dogs
>
> However, say a directory can be "wildcarded" with a star, like this:
>
> /animals/*/cats
> /animals/*/dogs
>
> If we select all four of the above URLs in a single statement, I want
> to order them **as if the directories were in different cells and I
> ordered ascending from the first cell, down to the last.**
>
> So I want those URLs back like this --
>
> /animals/*/cats
> /animals/domesticated/cats
> /animals/*/dogs
> /animals/domesticated/dogs
>
> -- since "*" (star) comes before the "d" in "domesticated." Put
> another way, I want SQL to evaluate the second directory in the string
> as a single value bound by the slashes ("*" or "domesticated") rather
> than a collection of characters.
>
> Effectively, I want to replicate the result that would occur if I had
> the data organized into different cells like this --
>
> field1 | field2 | field 3
> ----------------------------------
> animals | * | cats
> animals | * | dogs
> animals | domesticated | cats
> animals | domesticated | dogs
>
> -- then had this on my SQL: "ORDER BY field1, field2, field3".
>
> Possible?


or look at some of the examples at:
http://dev.mysql.com/doc/refman/5.0/...functions.html

to extract and "split" your data...

On Feb 11, 6:22 pm, Michael Austin <maus...@firstdbasource.com> wrote:
> Deane wrote:
> > (Sorry about the vague title, but I couldn't think of a title that
> > even remotely fit this situation.)
>
> > Consider a table with single column, consisting of URL paths in a
> > VARCHAR field, like this:
>
> > /animals/domesticated/cats
> > /animals/domesticated/dogs
>
> > However, say a directory can be "wildcarded" with a star, like this:
>
> > /animals/*/cats
> > /animals/*/dogs
>
> > If we select all four of the above URLs in a single statement, I want
> > to order them **as if the directories were in different cells and I
> > ordered ascending from the first cell, down to the last.**
>
> > So I want those URLs back like this --
>
> > /animals/*/cats
> > /animals/domesticated/cats
> > /animals/*/dogs
> > /animals/domesticated/dogs
>
> > -- since "*" (star) comes before the "d" in "domesticated." Put
> > another way, I want SQL to evaluate the second directory in the string
> > as a single value bound by the slashes ("*" or "domesticated") rather
> > than a collection of characters.
>
> > Effectively, I want to replicate the result that would occur if I had
> > the data organized into different cells like this --
>
> > field1 | field2 | field 3
> > ----------------------------------
> > animals | * | cats
> > animals | * | dogs
> > animals | domesticated | cats
> > animals | domesticated | dogs
>
> > -- then had this on my SQL: "ORDER BY field1, field2, field3".
>
> > Possible?
>
> select field1, field2, field3 from table where field1 = 'animals' and
> field3 in ('cats','dogs');
>
> or if it is a single field as in your initial query
>
> where field1 like '/animals/%/cats' or field1 like '/animals/%/dogs'
>
> or more broadly:
>
> where field1 like '%/cats' or field1 like '%/dogs'

I don't want specific rows. I want the all records in the table,
ordered as I specified.

Deane

Deane schreef:
> (Sorry about the vague title, but I couldn't think of a title that
> even remotely fit this situation.)
>
> Consider a table with single column, consisting of URL paths in a
> VARCHAR field, like this:
>
> /animals/domesticated/cats
> /animals/domesticated/dogs
>
> However, say a directory can be "wildcarded" with a star, like this:
>
> /animals/*/cats
> /animals/*/dogs
>
> If we select all four of the above URLs in a single statement, I want
> to order them **as if the directories were in different cells and I
> ordered ascending from the first cell, down to the last.**
>
> So I want those URLs back like this --
>
> /animals/*/cats
> /animals/domesticated/cats
> /animals/*/dogs
> /animals/domesticated/dogs
>
> -- since "*" (star) comes before the "d" in "domesticated." Put
> another way, I want SQL to evaluate the second directory in the string
> as a single value bound by the slashes ("*" or "domesticated") rather
> than a collection of characters.
>
> Effectively, I want to replicate the result that would occur if I had
> the data organized into different cells like this --
>
> field1 | field2 | field 3
> ----------------------------------
> animals | * | cats
> animals | * | dogs
> animals | domesticated | cats
> animals | domesticated | dogs
>
> -- then had this on my SQL: "ORDER BY field1, field2, field3".
>
> Possible?

"ORDER BY CONCAT(field1, '/', field2, '/', field3)"
will do the same ordering as in your 1st example

--
Luuk

On Tue, 12 Feb 2008 00:32:34 +0100, Deane <deane.barker@gmail.com> wrote:

> (Sorry about the vague title, but I couldn't think of a title that
> even remotely fit this situation.)
>
> Consider a table with single column, consisting of URL paths in a
> VARCHAR field, like this:
>
> /animals/domesticated/cats
> /animals/domesticated/dogs
>
> However, say a directory can be "wildcarded" with a star, like this:
>
> /animals/*/cats
> /animals/*/dogs
>
> If we select all four of the above URLs in a single statement, I want
> to order them **as if the directories were in different cells and I
> ordered ascending from the first cell, down to the last.**
>
> So I want those URLs back like this --
>
> /animals/*/cats
> /animals/domesticated/cats
> /animals/*/dogs
> /animals/domesticated/dogs
>
> -- since "*" (star) comes before the "d" in "domesticated." Put
> another way, I want SQL to evaluate the second directory in the string
> as a single value bound by the slashes ("*" or "domesticated") rather
> than a collection of characters.
>
> Effectively, I want to replicate the result that would occur if I had
> the data organized into different cells like this --
>
> field1 | field2 | field 3
> ----------------------------------
> animals | * | cats
> animals | * | dogs
> animals | domesticated | cats
> animals | domesticated | dogs
>
> -- then had this on my SQL: "ORDER BY field1, field2, field3".

It would be much simpler if it wasn't a 'set' string of '/animals/*/cats',
but some hierarchical table. Splitting strings is quite cumbersome in
MySQL. Would having a seperate hierachical table be possible?
--
Rik Wasmus