SQL problem

[limelect 48]

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

 

 

[limelect 48]

As i can see there is a 'SUBSTRING' so how to use it?

[Lars Fosdal 1793]

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?

[limelect 48]

@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 November 17, 2022 by limelect

[Lars Fosdal 1793]

I still think it is the underscore that plays a trick on you?

Ref. https://www.sqlitetutorial.net/sqlite-like/ - section "SQLite LIKE with ESCAPE clause"

[limelect 48]

@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 November 17, 2022 by limelect

[limelect 48]

@Lars Fosdal I invite you to enter my computer and see for yourself with our private communication

[limelect 48]

@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 November 17, 2022 by limelect

[Lars Fosdal 1793]

Did you try it with a ESCAPE '_' clause?

[limelect 48]

@Lars Fosdal YES

[Attila Kovacs 631]

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

 

[limelect 48]

@Attila Kovacs Reasons for my application

 

Edited November 17, 2022 by limelect

[Attila Kovacs 631]

that's not how things work, I'm not sure your database is worth search anything...

[limelect 48]

@Attila Kovacs It is not a nice response. You do not know my application and my professional reasoning.

[limelect 48]

@Lars Fosdal OK I put a very simple 1 line database with "like" statement

So check it out. Add or delete text and see for yourself

Compiled with D10.2.3 with source

TestLike.zip

[Lars Fosdal 1793]

The example does not exemplify the problem. (Delphi 11.1)

[limelect 48]

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 November 17, 2022 by limelect

[limelect 48]

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 November 17, 2022 by limelect

[Lars Fosdal 1793]

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.

[limelect 48]

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

[limelect 48]

@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

[Attila Kovacs 631]

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

 

[limelect 48]

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

I made a crazy software.

In any case, you helped a lot.Thanks

[Lars Fosdal 1793]

Did you try going 64-bit?

[limelect 48]

@Attila Kovacs Well thanks but you still use not nice words.

 

P.S did you start with D1 like me? And much much before.