Wildcard searchs in ASP SQL Statement?

[ynott]

I have a field in the database that supports up to 8 characters. I would like to use * as a wildcard type and ? as a wildcard type. So, if I used 4AWL* and 4AWL??99, the * is for anything after and the ?? is just for characters 5 and 6 in that field. So, for me, building this type of SQL statement seems close to impossible.

I was thinking of something like, if the left(1) character is a ?, then do whatever, or if left() is a * then do whatever, etc, etc.

But I don't know if that would even work. Any thoughts?

To keep this simple, my basic SQL statement would be:

SQL = "SELECT Details.VRM from ReadsDetails"
SQL = SQL & " WHERE Details.VRM LIKE '" & Ucase(Request.QueryString("code"))& "'"

"code" is the string that the user would input on the html document that would fire off the asp.

 

[foxbox]

This helpful for u:

http://www.techonthenet.com/sql/like.php

!

 

[vicvirk]

SQL = SQL & " WHERE Details.VRM LIKE '%" & Ucase(Request.QueryString("code"))& "%'"


--------

GOOGLE is a great resource to find answers to questions like "how do i..."

http://www.google.com/webhp?complete=1&hl=en

--------

 

[ynott]

Here's the last part of a SQL statement after the LIKE portion:

SQL1 = SQL1 & "' & A1 & A2 & A3 & A4 & A5 & A6 & A7 &"'"

This will take all 7 letters/numbers with success. However, I'm trying to create a wildcard in each of the 7 spots if it's called for. Ex, *ABC*23, or 1ABC12*, etc. Since a * caused a problem in the input querystring, I cheated and used a - in place of a * within the seven characters. Ex, -ABC-23, or 1ABC12-. I created an if/then statement, Ex,

if left(cc,1) = "-" then
A1 = "'%"
else A1 = '"'
end if

But as you can tell A1 can't handle how I need to label the % " and 'characters in the statement.

How can I get around this. I seem to be going around in circles.

 

[tsuji]

The question is not at all clear. Can you rephrase the question making sure you state clearly what is the characteristic of those seven "spots". Are they one character long and alphanumeric? Can they be empty? ... The wildcards for the sql are such as "%" and "_" that you seem to be ignoring despite being told. The problem also is that "*" and "?" as figuratively some wildcards we may understand loosely, but they may not coincident with the exact meaning of sql's "%" and "_". "%" can take zero-length character, ie empty, can "*" mean that? "_" must be one-character long and cannot be empty, can "?" mean that? ...

>... A1 can't handle how I need to label the % " and 'characters in the statement.
What possibly can it mean this sentence? Also if A1-A7 are alphanumeric, why should you worry quote, double-quote or percent sign's escaping?

If A1-A7 are alphanumeric and of one character long, also that you use "-" as a proxy of wildcard, I would guess something like this would be approximatively what would look like.
[tt]
s=A1 & A2 & A3 & A4 & A5 & A6 & A7
s=replace(s,"-","_")

SQL = "SELECT Details.VRM from ReadsDetails [red]Details[/red]"
SQL = SQL & " WHERE Details.VRM LIKE '" & s & "'"
[/tt]

 

[foxbox]

Maybe you must determine what the user has entered, and act on that. like this (not tested at all, and i'm sure i am incomplete. just to present you an idea):

'Step one: replace the ? 
cSearch = trim(replace(cSearch,"?","_"))

if left(cSearch,1) = "*" and right(cSearch,1) ="* then
 ' *ABC123*
 cSQL = "SELECT * FROM myTable where field1 like '%" & _
        cSearch & "%'"

elseif left(cSearch,1) = "*" then
 ' *ABCD123
 cSQL = "SELECT * FROM myTable where field1 like '%" & _
 cSearch & "'"

elseif right(cSearch,1) = "*" then
 ' ABCD123*
 cSQL = "SELECT * FROM myTable where field1 like '" & _ 
 cSearch & "%'"

elseif instr(cSearch,"*") <> 0 then
 ' ABC*123
 cSQL = "SELECT * FROM myTable where field1 like '" & _ 
 left(cSearch,instr(cSearch,"*")-1) & "%' OR " & _
 field1 like '%" & mid(cSearch,instr(cSearch,"*")+1) & ""

else
 cSQL = "SELECT * FROM myTable where field1 = " & cSearch
end if