Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2009
  • Last Modified:

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
0
belthasar
Asked:
belthasar
1 Solution
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now