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
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?
Any work around for this? Please Help

Bill

 

[zrzr]

I know it can be improved, but this is a code which can help you :

Public Function remove_non_alphanumeric(s As String) As String
Dim i As Integer
For i = 1 To Len(s) - 0
If Mid(s, i, 1) >= &quot;0&quot; And Mid(s, i, 1) <= &quot;9&quot; _
Or Mid(s, i, 1) >= &quot;a&quot; And Mid(s, i, 1) <= &quot;Z&quot; Then
remove_non_alphanumeric = remove_non_alphanumeric & Mid(s, i, 1)
End If
Next i
End Function

Then, in your query, instead of comparing the text with client_name, compare it with remove_non_alphanumeric(client_name)

It should have better ways, but this one works !!!

 

[billum]

Hi,

Thank you for the post.Is there any single SQL thatcan be used without the function ? Can somebody help ?

Thanks
Bill