SQL Query ,Help!

[billum]

Hi,

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_name is
"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?
Any work around for this.Please help !!

Bill

 

[cheerio]

My first reaction was that you had posted in the wrong forum and that the Access Queries and Jet SQL forum would be more appropriate.

However, I am not convinced this really is a SQL question as I do not see a pure SQL solution. What you really need is a custom VBA function that can be given a string and will return it without the non-alpha characters. Although that would work in theory your problem is that you risk trying to call the function for every row in the table and that would be very slow. If so you might be better off having a separate field called AlphaName which you populate when the row is first created. You would then search against that field and if such searches are common it could be indexed.

 

[Norris68]

You've picked a tricky one! I think the only way would be a public function (I seem to have replied with that every response today!)

Public Function AlphaOnly(InString As String) As String
Dim A As Long
Dim Char As String * 1
Dim S As String
For A = 1 To Len(InString)
Char = Mid$(InString, A, 1)
Select Case Char
Case "A" To "Z", "a" To "z", "0" To "9": S = S & Char
End Select
Next A
AlphaOnly = S
End Function

Then in your SQL statement, SELECT AlphaOnly([client_name]) FROM ... etc. This will not work in SQL server BE - only Access.