Jump to content
Sign in to follow this  
limelect

SQL problem

Recommended Posts

I have a text (it is a directory) I would like to find in a DB.

My SQL which is OK

 FDQuery4.SQL.add('WHERE MainDirectory LIKE ''%' + jvDirectoryEdit1.Text + '%'''

Now to the problem

If my DB text is  G:\Delphi Projects\image32_3.2.2\

and I am looking for 

G:\Delphi Projects\image32_3.2.2\

or 

G:\Delphi Projects\image32_3 

My above statement finds it NO PROBLEM

HOW EVER if my search is

G:\Delphi Projects\image32_3.2.2\Examples\Layers101

My above line does NOT find it.

What I also tried is using CHARINDEX which I may be did not use correctly

 

So w.hat I am looking for is search text WITHIN my DB text

Any idea thanks

 


 

Share this post


Link to post

Could there be some sort of escape handling kicking in?

Is it necessary to pre-process the text you send as parameter to ensure it is correctly handled by SQLite?

Share this post


Link to post

@Lars Fosdal NO escape but i see there are 2 more possibilities

SUBSTRING and  containing

from 

containing does not work unless i do not know how to use it

 

Edited by limelect

Share this post


Link to post

@Lars Fosdal this F7 full statement output that works

 

'Select SectionsId,MainDirectory,MyDateTime,ProjectName,ProjectrealName,Category,FileMask ,Description,FilesIndex'#$D#$A'from Files,Projects'#$D#$A'WHERE MainDirectory LIKE ''%G:\Delphi Projects\image32_3.2.2%''AND SectionsId=FilesIndex'

 

As you see no problem

Edited by limelect

Share this post


Link to post

@Lars Fosdal Unfortunately all those 'like' explanations are known to me

 

Or would you like me to make a small DB source to examine the problem?

and put it here?

Edited by limelect

Share this post


Link to post

Why would a search for "G:\Delphi Projects\image32_3.2.2\Examples\Layers101" find "G:\Delphi Projects\image32_3.2.2\" ?

 

Share this post


Link to post

yes just add a few characters to the text and it will not find the item (a record)

and this is what I want or delete some text and it will find

see my first  writings WITHIN my DB text

7 minutes ago, Lars Fosdal said:

The example does not exemplify the problem.

 

Edited by limelect

Share this post


Link to post

Maybe I will clarify my problem I add the base directory to the database.

But this directory has many subdirectories and if one searches for a subdirectory

he still needs to find the main directory. This is why

my search is like that.

 

Do you think there is another way to solve the problem?

Edited by limelect

Share this post


Link to post

My bad for not reading your example closely enough.

 

The problem is that you are not searching for a substring

%G:\Delphi Projects\image32_3.2.2\Examples\Layers101% is a superset that contains values that are NOT in the database.  

You want to find out if your DB text is contained in your parameter - which is completely different thing.

 

  FDQuery1.SQL.add('Select MainDirectory');
  FDQuery1.SQL.add('from Files');
  FDQuery1.SQL.add('WHERE INSTR(''' + Edit1.Text + ''', MainDirectory) > 0');
 

The above will return lines where the MainDirectory strings are found as part of the string in Edit1.Text.

  • Like 1

Share this post


Link to post

Delete some chare and it WII NOT find part of the text

The Like finds if you delete some char

INTR find if you add char

maybe i will try to OR both

Share this post


Link to post

@Lars Fosdal BIG BIG THANKS

  FDQuery1.SQL.add('WHERE (MainDirectory LIKE ''%' + Edit1.Text + '%'') OR INSTR(''' + Edit1.Text + ''', MainDirectory) > 0'  );
I will check it on the real software

Share this post


Link to post

why do you need the 2 different compare functions? why not just 

 

WHERE (A LIKE '%' + B + '%') OR (B LIKE '%' + A + '%') ?

 

if you read it out loud, everybody beside to you will understand what you want

 

Share this post


Link to post

Well, it returns me to my original problem OUT OF MEMORY.

I made a crazy software.

In any case, you helped a lot.Thanks

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×