limelect 48 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 As i can see there is a 'SUBSTRING' so how to use it? Share this post Link to post
Lars Fosdal 1792 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 (edited) @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 Share this post Link to post
Lars Fosdal 1792 Posted November 17, 2022 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" Share this post Link to post
limelect 48 Posted November 17, 2022 (edited) @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 Share this post Link to post
limelect 48 Posted November 17, 2022 @Lars Fosdal I invite you to enter my computer and see for yourself with our private communication Share this post Link to post
limelect 48 Posted November 17, 2022 (edited) @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 Share this post Link to post
Lars Fosdal 1792 Posted November 17, 2022 Did you try it with a ESCAPE '_' clause? Share this post Link to post
Attila Kovacs 629 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 (edited) @Attila Kovacs Reasons for my application Edited November 17, 2022 by limelect Share this post Link to post
Attila Kovacs 629 Posted November 17, 2022 that's not how things work, I'm not sure your database is worth search anything... Share this post Link to post
limelect 48 Posted November 17, 2022 @Attila Kovacs It is not a nice response. You do not know my application and my professional reasoning. Share this post Link to post
limelect 48 Posted November 17, 2022 @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 Share this post Link to post
Lars Fosdal 1792 Posted November 17, 2022 The example does not exemplify the problem. (Delphi 11.1) Share this post Link to post
limelect 48 Posted November 17, 2022 (edited) 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 Share this post Link to post
limelect 48 Posted November 17, 2022 (edited) 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 Share this post Link to post
Lars Fosdal 1792 Posted November 17, 2022 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. 1 Share this post Link to post
limelect 48 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 @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
Attila Kovacs 629 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 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
limelect 48 Posted November 17, 2022 @Attila Kovacs Well thanks but you still use not nice words. P.S did you start with D1 like me? And much much before. Share this post Link to post