Solved

Delphi - Paradox Query / LIKE command

Posted on 2007-03-31
15
1,910 Views
Last Modified: 2010-04-05
Hi

I am using the LIKE SQL command in Delphi - paradox table and my query is :
Select * from
Table
where Field1 like : '%Field1%'
order by Field2,Field3
But I am not getting any result.
I read in the internet that the LIKE command cann't be used with Variables , but only with static string , is it true and if so what can i do to get the kind of search i want it in my program. what i want is if a user typed a word of a sentence i shall get the whole sentence like
sentence : ' macnhester defeated chelsea 2 to nil '
so if the user wrote defeat in the search field then he will get the previous sentence or any other matches

thanks
0
Comment
Question by:ibrobar
  • 5
  • 4
  • 3
  • +2
15 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828297
I have 2 potential ideas:
* try using * instead of %

* >where Field1 like : '%Field1%'
I assume that you meant
where Field1 like : '%defeat%'
0
 

Author Comment

by:ibrobar
ID: 18828310
Hi again

I have field called Description and the query will be :
select * from TreeData
where Description like :'%Description%'
order by date1,month1
So i am using variable cause i don't know afourse the value i am looking for

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 18828409
you mean "Description" is the name of the field AND of the variable?
when/how is that variable filled?
0
 
LVL 10

Expert Comment

by:dinilud
ID: 18828457
Try this

select * from TreeData
where Description like   '%'+  :Description +'%'
order by date1,month1
0
 
LVL 6

Expert Comment

by:Hardi
ID: 18828524
what if you remove the colon?
where Description like '%Description%'
0
 
LVL 2

Expert Comment

by:uaexpert
ID: 18834630
Actually this way it must work :


select * from TreeData
where Description like   '%' +  Description + '%'
order by date1,month1

but this query is just useless because the condition in WHERE clause will always return True.
It can have some sense if you will compare one field with some other field, e.g.:
WHERE Description LIKE '%' + SearchWord + '%'
(here SearchWord is another field in your table).
0
 

Author Comment

by:ibrobar
ID: 18841577
Hi

Dinilud I will try your solution today.
Uaexpert I want to choose the value of the variable field during run time cause i am doing a search during runtime  up to the user demand.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 2

Accepted Solution

by:
uaexpert earned 50 total points
ID: 18841624
In such case you can use query parameter as dinilud suggested or simply create query expression "on the fly" using the value of your variable. Example:

var
  SearchWord : string; //we suppose the word entered by user is stored here
  SQL : string; //here we will store necessary SQL statement
  Query1 : TQuery;
  . . . . . .
begin
  Query1 := TQuery.Create(nil);
  Query1.DatabaseName := <Put your database name here>;
  SQL := 'select * from TreeData where Description like   ''%' + SearchWord + '%'' order by date1,month1';
  Query1.SQL := SQL;
  Query1.Open;
  //read result from Query1

0
 
LVL 6

Expert Comment

by:Hardi
ID: 18842372
Actually I don't see anything wrong with your query ibrobar.
It's just the colon is not necessary.
Does yours work if you replace LIKE with = ?
eg. where Field1 = 'macnhester defeated chelsea 2 to nil '

If it doesn't then follow uaexpert's example. You can also use the function QuotedStr for quoting.
eg.
Query1.SQL := 'select * from myTable where Description like ' +QuotedStr('%'+searchWord+'%')+ ' order by field1,field2';
0
 
LVL 6

Expert Comment

by:Hardi
ID: 18842390
Forget about QuotedStr... I still think double quotes is better =P
like uaexpert's example
0
 
LVL 6

Expert Comment

by:Hardi
ID: 18842402
btw it should be Query1.SQL.Text instead of just Query1.SQL =P
0
 
LVL 2

Expert Comment

by:uaexpert
ID: 18842784
Yes Hardi is right.
It must be:
  . . . . . .
  Query1.SQL.Text := SQL;
  . . . . . .

Actually it will be good addition to replace any single quote in SearchWord by two quotes to prevent any error if SearchWord contains quotes. Again it will not be necessary if you will use query with parameters.
0
 

Author Comment

by:ibrobar
ID: 18855534
Hi all
Sorry for the delay in replying , cause i was in my days off.
I tried this soultion :  Select * from Table where Field like : '%'+Field+'%'
but it didn't work.
I think the solution about creating a query during runtime might work , I will try it today

thanks a lot all
0
 
LVL 6

Expert Comment

by:Hardi
ID: 18855767
Why are you still using the colon... =P
If it still doesn't work, give us the full string of your query, or the code
0
 

Author Comment

by:ibrobar
ID: 18862532
Thanks UaeExpert

I tried your solution and it's working 100 percent.
thanks a lot
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction The parallel port is a very commonly known port, it was widely used to connect a printer to the PC, if you look at the back of your computer, for those who don't have newer computers, there will be a port with 25 pins and a small print…
Introduction I have seen many questions in this Delphi topic area where queries in threads are needed or suggested. I know bumped into a similar need. This article will address some of the concepts when dealing with a multithreaded delphi database…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now