SQL Query , Pls Help !!

[billum]

Hi,

I had posted this once and couldnt get a SQL solution

The DB in question is an ACCESS 2000 DB having a table
named client having a field which stores client names.
This field can have any character in it. The problem
is to find names avoiding non alphanumeric characters
from it.
Pls consider the following example data

client_name
-----------
A B C Ltd.
ABC Ltd.
A.B.C. Ltd

We need to find all info even if the client
gives "abc ltd" or "abcl".

One solution is to use the LIKE operator to form an
sql like this

SELCET * ... where Client_name LIKE
"a[!a-z0-9]b[!a-z0-9]c[!a-z0-9]l*" OR client_name LIKE
'abc l*'

where we check for any non-alphanumeric character
between every letter in the search phrase. But here
the problem is it will not pick the third data as it
has two non-alphanumeric chars before the word "ltd".
The ideal situation will be to use the "[!a-z0-9]"
part to search a zero or more occurances somewhat like
the following

SELCET * ... where Client_name LIKE
"a{[!a-z0-9]*}b{[!a-z0-9]*}...*"

The data inside the {} will be checked for zero or
more occurences (the * operator). Unfortunately its
not working with Access.

Any idea how it can achieved with Access SQL?
I would like to have a SQL query ie not involving any user
defined functions.

Thanks
Bill

 

[IFELSETHEN]

Hi,
Sounds like you can’t do this in one pass. I would suggest that you create a temp table with the client name without the non alphanumeric characters and then on the second pass you can do the LIKE statement. With the fast CPU’s that we are now using this will only be a few seconds of wait for the client.

Regards, John

*********************
John Nyhart

http://www.nyhart.com

*********************