Using LIKE operator and wildcard for Mysql Query 1

[lexer]

Hi

I`m using VB 6.0 for making a mysql query (ADO connection) with LIKE operator and the wildcard %, But I don´t know what is the correct syntax, I've got a Mysql table called codes with numbers and each number has an ID, I want to match the mysql numbers with a varibale called Numberx, for example:

Numberx is: 5856561

and the codes table has the following fields:

code Id
5856 100
5857 101
5858 102
108 103
109 104

Numberx matches with number (code) 5856 and its ID is: 100, I'm trying to use VB mysql wildcard "%" for I dont know the correct syntax.
This is my VB code:

Numberx = Mid(Number, 1, 3)
Set Rs1 = Cnx.Execute("select COUNT(*) as count from codes where code LIKE " & Numberx) 'I'm know how to put wildcard %
    countdr = Rs1.Fields("count")
    If countdr <> 0 Then
        IdNum = Cnx.Execute("select * from codes where code=" & Numberx).Fields("Id")  
  End If

Does somebody know a web page with VB 6.0 intructions for mysql

Thanks!!

 

[gmmastros]

In SQL, wildcard searches are accomplished by using the % symbol. Basically, % means any character and any number of characters. Since you will be searching from the beginning of the code, you should only put your % symbol on the end. This will causes matches to occur only if both strings start out the same. So, something like this:

[tt][blue]
Set Rs1 = Cnx.Execute("select COUNT(*) as count from codes where code LIKE [!]'[/!]" & Numberx [!]& "%'"[/!])
[/blue][/tt]

Notice that I put single-quotes in your query. This is required for SQL Server, but may be different in MySQL. If you ever want to search anywhere within the string, then you would want to put the % symbol on both sides of the data. Queries the search from the beginning CAN be faster than searches anywhere in the string because an index on the Code column would be used. For more information on this, do a google search on "SQL Sargable like search".

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[lexer]

Thanks gmmastros!! It works