ADO sql wildcard "Like" queries - Search DB table allowing 0 (zero) to be O (letter O).

[Dave Smithz]

Hello there,

Summary:
How far can you go with SQL Select queries using like clauses with wildcard
characters. Can you apply anything like regular expressions?

Full details:

On a Intranet website, I request a code from a user which is then compared
with a code in an ADO access database.

The common problem that has been happening though is that users are getting
0 and O's mixed up so I want to ignore this when it happens.

E.g.
User enters
RCxT0wR when it should be RCxTOwR

A match is checked by doing a select on a database table, therefore:
Select * from CodeTable where code = "USER-INPUT"

if there are any row matches then the code is correct.

With regard to the 0 and O issue I first thought of using a "Like" query
and replacing all users "O" or "0" with a % character (single wild card I
believe) but then it dawned upon me that a user only had to enter all 0's
and they will match every code.

I was wondering if it is possible in ADO SQL, if you can sort of apply
regular expression type syntax to a like clause.

Therefore, I change the like string to something like

Select * from CodeTable where code = "USER-INPUT"
but I change "USER-INPUT" to be that any occurrence of a 0 or O is changed
to [O/0] where [O/0] means, any single character at that position can be O
or 0.

If this is not possible, then I will have to take each code from the
database one at a time, and convert all O's to 0's and then do the same in
the user entered code. but this means a like for like comparison with each
entry in the database.

Also I cannot change all the entries in the database to already be 0's only
because I need to maintain the original code.

I hope this makes sense and I welcome any suggestions.

TIA

Dave

[Steve Jorgensen]

Replace all the Os with [O0]. A string of characters within brackets means to
match any character in the list. This works in VBA, JET, and SQL Server.

On Thu, 13 Nov 2003 16:42:27 -0000, "Dave Smithz" <Spam Free> wrote:

Hello there,

Summary:
How far can you go with SQL Select queries using like clauses with wildcard
characters. Can you apply anything like regular expressions?

Full details:

On a Intranet website, I request a code from a user which is then compared
with a code in an ADO access database.

The common problem that has been happening though is that users are getting
0 and O's mixed up so I want to ignore this when it happens.

E.g.
User enters
RCxT0wR when it should be RCxTOwR

A match is checked by doing a select on a database table, therefore:
Select * from CodeTable where code = "USER-INPUT"

if there are any row matches then the code is correct.

With regard to the 0 and O issue I first thought of using a "Like" query
and replacing all users "O" or "0" with a % character (single wild card I
believe) but then it dawned upon me that a user only had to enter all 0's
and they will match every code.

I was wondering if it is possible in ADO SQL, if you can sort of apply
regular expression type syntax to a like clause.

Therefore, I change the like string to something like

Select * from CodeTable where code = "USER-INPUT"
but I change "USER-INPUT" to be that any occurrence of a 0 or O is changed
to [O/0] where [O/0] means, any single character at that position can be O
or 0.

If this is not possible, then I will have to take each code from the
database one at a time, and convert all O's to 0's and then do the same in
the user entered code. but this means a like for like comparison with each
entry in the database.

Also I cannot change all the entries in the database to already be 0's only
because I need to maintain the original code.

I hope this makes sense and I welcome any suggestions.

TIA

Dave

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Yes, you can use "... LIKE 'RCxT[O|0]wR' ... "

Use the "up-and-down" line between the zero and the oh - it means
logical OR.

MGFoster:::mgf
Oakland, CA (USA)
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP7RZq4echKqOuFEgEQJMCwCgmthMYyWUHNefqy3YSw4LYr 939coAoKOW
YF1Bq72K7HNtD4Tp0yZ/gNif
=nc9d
-----END PGP SIGNATURE-----

Dave Smithz wrote:

Hello there,

Summary:
How far can you go with SQL Select queries using like clauses with wildcard
characters. Can you apply anything like regular expressions?

Full details:

On a Intranet website, I request a code from a user which is then compared
with a code in an ADO access database.

The common problem that has been happening though is that users are getting
0 and O's mixed up so I want to ignore this when it happens.

E.g.
User enters
RCxT0wR when it should be RCxTOwR

A match is checked by doing a select on a database table, therefore:
Select * from CodeTable where code = "USER-INPUT"

if there are any row matches then the code is correct.

With regard to the 0 and O issue I first thought of using a "Like" query
and replacing all users "O" or "0" with a % character (single wild card I
believe) but then it dawned upon me that a user only had to enter all 0's
and they will match every code.

I was wondering if it is possible in ADO SQL, if you can sort of apply
regular expression type syntax to a like clause.

Therefore, I change the like string to something like

Select * from CodeTable where code = "USER-INPUT"
but I change "USER-INPUT" to be that any occurrence of a 0 or O is changed
to [O/0] where [O/0] means, any single character at that position can be O
or 0.

If this is not possible, then I will have to take each code from the
database one at a time, and convert all O's to 0's and then do the same in
the user entered code. but this means a like for like comparison with each
entry in the database.

Also I cannot change all the entries in the database to already be 0's only
because I need to maintain the original code.

I hope this makes sense and I welcome any suggestions.

TIA

Dave