Operator in SQL like "Like"

[Guest]

I need to find an operator I can use with programming - the help says I can't
use the "Like" statement, but I need to get the same results this would give
me - is there an operator I can use?
Thank you!

 

[Dirk Goldgar]

I need to find an operator I can use with programming - the help says
I can't use the "Like" statement, but I need to get the same results
this would give me - is there an operator I can use?
Thank you!

Could you explain your question a bit more? There's a Like operator in
SQL, and there's a Like operator in VBA. What are you trying to do, and
what leads you to believe you can't do it?

 

[Guest]

I am trying to write a program in Delphi that will use an Access query to
find some data. If I was just using Access, I could say for it to search for
' Like "*(searchitem)*" ', which would retrieve all records that had the
string of letters in the search item, not just those which were exactly equal
to the search item. But I can't use this operator in Delphi, it returns
nothing, so is there an operator I can use that will be compatible, and give
the results that Like gives? (I'm assuming that Delphi will accept the same
commands that Visual Basic etc will accept, and as Delphi won't accept Like
and it says in the MS Access help that Visual Basic, C++ etc won't accept it
either that seems to make sense)
thank you!

 

[Dirk Goldgar]

I am trying to write a program in Delphi that will use an Access
query to find some data. If I was just using Access, I could say for
it to search for ' Like "*(searchitem)*" ', which would retrieve all
records that had the string of letters in the search item, not just
those which were exactly equal to the search item. But I can't use
this operator in Delphi, it returns nothing, so is there an operator
I can use that will be compatible, and give the results that Like
gives? (I'm assuming that Delphi will accept the same commands that
Visual Basic etc will accept, and as Delphi won't accept Like and it
says in the MS Access help that Visual Basic, C++ etc won't accept it
either that seems to make sense)

I'm afraid I'm still not following you. VB most definitely does contain
the Like operator, though of course it doesn't exist in C++. Would you
mind pointing me to the help topic that leads you to think the Like
operator doesn't exist in Visual Basic?

Now, as to using an Access query from Delphi, and using the "Like" SQL
operator in it. You ought to be able to do that, though I'm not
familiar with Delphi and don't know if it imposes its own restrictions.
But Jet SQL certainly understands the Like operator. If you are using
the Microsoft OLE DB Provider for Jet to execute your query, you have to
use a different set of wild-card characters. In particular, the "match
any 0 or more characters" wild-card character becomes '%' instead of
'*'. So in that case, instead of the pattern "*(searchitem)*", you
would use "%(searchitem)%".

Maybe that's all that's wrong with what you're doing. Try changing the
wild-card characters, and if that doesn't work, post back with the
actual code you're trying to execute. I'm not familiar with Delphi, but
I may be able to figure out what you should be doing.

 

[Guest]

I tried changing the wildcard characters and it didn't recognise them so I
had to change them back.

I have an Access database with a table called "product". I want to create a
query that uses this. The SQL that Access gives me for the query I am trying
to create is this:

SELECT [Product Table].ProdID, [Product Table].Supplier, [Product
Table].Descr, [Product Table].CustomerPrice
FROM [Product Table]
WHERE ((([Product Table].Descr) Like "*clock*"))
ORDER BY [Product Table].ProdID;

But I want to use this through Delphi. To do this, I have to use an ADO
query. I don't know if this is possible as the Access help says this:
"The Microsoft Jet database engine uses the Microsoft® Visual Basic® for
Applications (or VBA) expression service to perform simple arithmetic and
function evaluation. All of the operators used in Microsoft Jet SQL
expressions (except Between , In , and Like ) are defined by the VBA
expression service."
So it may be that the Like operator for Access SQL cannot be used in Delphi.

But the code I am trying to write to do this is this:

begin
ADOQryDescr.close;
ADOQryDescr.Parameters[1].Value := edtDescr.Text;
with ADOQryDescr.SQL do
begin
clear;
add('SELECT [Product Table].ProdID, [Product Table].Supplier,
[Product Table].Descr, [Product Table].CustomerPrice FROM [Product Table]');
add('WHERE (([Product Table].Descr Like "* descr)*")');
add('ORDER BY [Product Table].ProdID;');
end;
ADOQryDescr.Active:=true;
ADOQryDescr.Open;
end;

(I am passing the text using a parameter, descr, which is defined correctly)
And it doesn't work, so I don't know if it's a problem with my code, or with
Delphi not being able to use the Like operator, or what, and whether or not
there is another operator I can use instead? Thank you!

 

[Dirk Goldgar]

(comments inline)

I tried changing the wildcard characters and it didn't recognise them
so I had to change them back.

I have an Access database with a table called "product". I want to
create a query that uses this. The SQL that Access gives me for the
query I am trying to create is this:

SELECT [Product Table].ProdID, [Product Table].Supplier, [Product
Table].Descr, [Product Table].CustomerPrice
FROM [Product Table]
WHERE ((([Product Table].Descr) Like "*clock*"))
ORDER BY [Product Table].ProdID;

But I want to use this through Delphi. To do this, I have to use an
ADO query. I don't know if this is possible as the Access help says
this: "The Microsoft Jet database engine uses the Microsoft® Visual
Basic® for Applications (or VBA) expression service to perform simple
arithmetic and function evaluation. All of the operators used in
Microsoft Jet SQL expressions (except Between , In , and Like ) are
defined by the VBA expression service."
So it may be that the Like operator for Access SQL cannot be used in
Delphi.

No, that's just saying that the Between, In, and Like operators are
defined by Jet SQL, not by the VBA expression service, so you *can* use
them in non-Access queries.

But the code I am trying to write to do this is this:

begin
ADOQryDescr.close;
ADOQryDescr.Parameters[1].Value := edtDescr.Text;
with ADOQryDescr.SQL do
begin
clear;
add('SELECT [Product Table].ProdID, [Product
Table].Supplier, [Product Table].Descr, [Product Table].CustomerPrice
FROM [Product Table]'); add('WHERE (([Product
Table].Descr Like "* descr)*")'); add('ORDER BY
[Product Table].ProdID;'); end;
ADOQryDescr.Active:=true;
ADOQryDescr.Open;
end;

(I am passing the text using a parameter, descr, which is defined
correctly) And it doesn't work, so I don't know if it's a problem
with my code, or with Delphi not being able to use the Like operator,
or what, and whether or not there is another operator I can use
instead? Thank you!

My guess is that your parameter is not being recognized as such inside
the literal string pattern "* descr)*". You could approach this one
of two ways. You might try breaking the pattern up into concatenated
pieces within the SQL, like this:

add('WHERE (([Product Table].Descr Like "*"+descr)+"*")');

Or, since you know the value of the parameter [descr] at the time you
are buildiong the SQL, just drop the parameter altogether and build that
value right into the SQL. I'm not conversant with Delphi, but I guess
it might look something like this:

------- start of code -- probably not exactly correct -------
begin
ADOQryDescr.close;
with ADOQryDescr.SQL do
begin
clear;
add('SELECT [Product Table].ProdID, [Product
Table].Supplier,
[Product Table].Descr, [Product Table].CustomerPrice FROM [Product
Table]');
add('WHERE (([Product Table].Descr Like
"*'+edtDescr.Text+'*")');
add('ORDER BY [Product Table].ProdID;');
end;
ADOQryDescr.Active:=true;
ADOQryDescr.Open;
end;

(more code here)
------- end of code -------

 

[Guest]

Docmd.RunSQL “DELETE CASE_TARGETS.FIRST_NAME FROM CASE_TARGETS WHERE
CASE_TARGETS.FIRST_NAME like “ & “’*†[Form_XP]![FIRST_NAME] & “*’;â€

Note the " & " '*" & "John" & "*';" Resolves to like '*John*'

Have you ever wondered what the logic was for nesting the single quote?

I did not test it. You might begin having fun replacing paramaters in a SQL
string.