SQL Wildcard Problem

I am using OLEDB with VB.NET and I'm trying to make a like statement that will pull all records from an Access database that does not start with a letter. This works in Access: LIKE '#*'

I know the wildcards are different for ADO so I have tried these:
LIKE '[^ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
LIKE '[!ABCDEFGHIJKLMNOPQRSTUVWXYZ]%'
LIKE '[^A-Z]%'
LIKE '#%'  <--  Gives no error but no resultes eather. There are no records that start with the # symbol.

I even tried these and they errored
LIKE '[0123456789]%'
LIKE '[38]%'

Are brackes not supported or am I just missing the obvious?

One of my resources: http://office.microsoft.com/en-us/assistance/HP051881851033.aspx
belthasarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HilaireCommented:
Don't know access very well,
but you could try

WHERE ISNUMERIC(LEFT(<yourcolumn>, 1))
or
WHERE ISNUMERIC(LEFT(<yourcolumn>, 1)) = 1 (or true ?)
0
chanitoCommented:
This looks right to me:  LIKE '[^A-Z]%'  or NOT LIKE '[A-Z]%'

but since it's not working you can always try this instead:

LEFT(yourfield,1) NOT BETWEEN 'A' AND 'Z'

or LEFT(yourfield,1) NOT LIKE '[A-Z]'  maybe...
0
belthasarAuthor Commented:
LEFT(yourfield,1) NOT LIKE '[A-Z]' gives me: The expression contains undefined function call LEFT().
NOT LIKE '[A-Z]%' gives me: Error in Like operator: the string pattern '[A-Z]%' is invalid.

Heres my code:
Dim ListingData As New DataSet("Listings")
ListingData = Query("SELECT * FROM Directory ORDER BY company1")
Dim LikeListings As DataRow()
LikeListings = ListingData.Tables(0).Select("company1 NOT LIKE '[A-Z]%'")

However, this works like pie to get all that start with A:
LikeListings = ListingData.Tables(0).Select("company1 NOT LIKE 'A%'")
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

chanitoCommented:
Try MID(company,1,1) instead of LEFT() or SUBSTR(company,1,1).

Also instead of the between or isnumeric() you could try

INSTR('ABCDEFGHIJKLMNOPQRSTUVWXYZ',MID(company,1,1)) = 0
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcmonapCommented:
Hi belthasar.

Does this do what you want?  I think perhaps you need the square brackets in access, this certainly works as an access query

SELECT d.*
FROM Directory AS d
WHERE Left([company1],1) Not Like "[a-Z]";
0
belthasarAuthor Commented:
It's not liking MID, INSTR, or SUBSTR eather, but SUBSTRING works. So I tried this:
SUBSTRING(company1,1,1) NOT LIKE '[A-Z]'
and got: Error in Like operator: the string pattern '[A-Z]' is invalid.

I found this: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatadatacolumnclassexpressiontopic.asp
It dosent look like brackets are supported.
0
belthasarAuthor Commented:
OK, I know thisis sloppy, but it works:

Dim filter As String
Dim ascii As Short
For ascii = 65 To 90
      If filter <> "" Then filter &= " AND "
      filter &= "SUBSTRING(company1,1,1) NOT LIKE '" & Chr(ascii) & "'"
Next
LikeListings = ListingData.Tables(0).Select(filter)

<b>[&nbsp;LOL&nbsp;]</b>
0
chanitoCommented:
Seems SQL syntax for Jet ADO is somewhat of a mystery...

This ought to work:

SUBSTRING(company,1,1) NOT BETWEEN 'A' AND 'Z'
0
belthasarAuthor Commented:
0
chanitoCommented:
I didn't realize you were using .NET

How about:

CONVERT(SUBSTRING(company,1,1),'System.Int32') > 0

Of course this only returns values starting with numbers... don't know if you have other characters.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.