• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2587
  • Last Modified:

Delphi - Paradox Query / LIKE command

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
ibrobar
Asked:
ibrobar
  • 5
  • 4
  • 3
  • +2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I have 2 potential ideas:
* try using * instead of %

* >where Field1 like : '%Field1%'
I assume that you meant
where Field1 like : '%defeat%'
0
 
ibrobarAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you mean "Description" is the name of the field AND of the variable?
when/how is that variable filled?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
diniludCommented:
Try this

select * from TreeData
where Description like   '%'+  :Description +'%'
order by date1,month1
0
 
HardiCommented:
what if you remove the colon?
where Description like '%Description%'
0
 
uaexpertCommented:
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
 
ibrobarAuthor Commented:
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
 
uaexpertCommented:
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
 
HardiCommented:
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
 
HardiCommented:
Forget about QuotedStr... I still think double quotes is better =P
like uaexpert's example
0
 
HardiCommented:
btw it should be Query1.SQL.Text instead of just Query1.SQL =P
0
 
uaexpertCommented:
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
 
ibrobarAuthor Commented:
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
 
HardiCommented:
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
 
ibrobarAuthor Commented:
Thanks UaeExpert

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

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

  • 5
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now