Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Problem with LIKE operator

HI,

i want to search wild characters along with the combination of other characters. how do i do that using LIKE operator. i am using Access 7.0. Ex : if the value of the field in the table is having values as, Hello and ?Hello. I want to search the string "?Hello" only. I tried thhis LIKE statement.
SELECT *
FROM <Table>
WHERE<Field> = "bc"
AND <Field1>  LIKE (chr(63) &"*Hello*");
 But this retrieves both the records  with "Hello" and "?Hello".
Similarly i want to use other wild characters(overriding their default meaning). Any suggestions.?

Thanks,
ysgr
0
ysgr
Asked:
ysgr
  • 3
  • 2
  • 2
  • +2
1 Solution
 
aburrCommented:
Leave the * out of "*hello*"
0
 
ysgrAuthor Commented:
Hi aburr,

i tried removing * in the LIKE statement, which retrieves zero records.
Any other solution?

Thanks,
ysgr
0
 
TrygveCommented:
Try this SQL:  SELECT [Hello Table].SomeID, [Hello Table].Description
FROM [Hello Table]
WHERE ((([Hello Table].SomeID)=Chr(63) & "Hello"));

Hope this helps !
Trygve
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
johnsenCommented:
The special characters left bracket ([), question mark (?), number sign (#), and asterisk (*) can be used to match themselves directly only by enclosing them in brackets.  The right bracket (]) can't be used within a group to match itself, but it can be used outside a group as an individual character.

Try this SQL:

SELECT *
FROM TEST
WHERE (((TEST.Field1) Like '*[?]Hello*'));

I think this helps!
Johnsen
0
 
SkorCommented:
I tested Like "[?]Hello" as criteria and only got "?Hello", I did not get "*Hello" or "Hello".

--Skor
0
 
TrygveCommented:
My proposed answer also gives you the correct records, as far as I can see. But of course the "bracket solution" is more visual and perhaps easier to understand.
0
 
johnsenCommented:
OKIDO... Trygve
0
 
ysgrAuthor Commented:
IT works for wild-characters, still i have problems for chracter |(OR operator).

Thanks,
ysgr
0
 
TrygveCommented:
The vertical bar | can't be tested for using the bracket solution. You will have to do something like this:

SELECT [Special Characters].TestField, [Special Characters].Description
FROM [Special Characters]
WHERE ((([Special Characters].TestField)=Chr(124) & "Hello"));

Hope this helps !
Trygve
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

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