Select Query Help

[ashar5]

Hi,

Assume, I have table which consists of 5 values, say

C:/Testing/test1/
C:/Testing/test1/test2/
C:/Testing/1/
C:/Testing/
C:/1/

I want to query the database table to get the values starts from 'C:/Testing/' and '/' as a escape character.

ie, I want to get only two things,

C:/Testing/test1/
C:/Testing/1/

Both starts with 'C:/Testing/' and the next escape character is '/' .

________________________

Similarly, if query the database which starts with 'C:/' it should give the following details:

C:/Testing/
C:/1/

I have tried LIKE query and REGEXP queries. But seems no luck.

Could any one update me, is there any way to do it.

Thanks in Advance,
Rahul.

[smartness]

$sql = Select * from `table` WHERE `column`like 'c:/testing/%' LIMIT 2?

Is this what you want to achieve?

[ashar5]

Thanks for your reply.

But that is not my requirement.

If you put Like query means, it will list all the items.. Even if you put limit , it wont work, if I get results by ascending or descending order.

I put those values (say C:/Testing....) as a sample examples.

My requirement is, the result set should starts with a string (say C:/) and it should ends with the next escape character '/'.

Hence it should only return,

C:/Testing/
C:/1/

ie, strings Starts with 'C:/' and some characters and then ends with '/'.

The query should not list the other values like,

C:/Testing/test1/
C:/Testing/test1/test2/
C:/Testing/1/

Because, the above strings contain two escape characters ('/') after 'C:/'

_______________________________________________________

For an example,

if we put like query with like query with 'C:/%' it will return all the text which starts with 'C:/' . What I need is, if we put like with 'C:/%/' , ie, starts with 'C:/' and should ends with the next '/' character. ie, Taking '/' as a escape character for like '%' query.

_______________________________________________________

Hope, you understand my requirement with this email.

Awaiting your update.

[smartness]

Oh now i Understand What you are trying to do!

Sorry, I don't know how to achieve it with mysql (I think it's not possible)

How about fetching all rows in a array, and then unset those who have more than 2 slashes! (C:/test/)

[ashar5]

Yes, Currently I am doing the same you have mentioned. ie, Get all those things in a array, and get the correct values.

I will continue my work. If you know it later, please reply to this thread.

And once again, thanks for your inputs.

[smartness]

Well i think it's better so!

as far as I know letting PHP handle them is faster than querying the db with a lot of where/like conditions!

[ashar5]

Yes, but if there is a lot of sub directories then querying with 'like' will avoid unwanted data processing.

Anyway thanks for your inputs.

[ashar5]

Hi,

I found a sample query..

select SUBSTRING_INDEX(COLUMN, '/', -4) like 'C:/Testing/%/';

It should return the value that I required.

where -4, if no. of slashes + 1.