sql question

[robojob]

i know that there are parameters such as like, =, or, etc but is there a contains?

i need to search my database using serial numbers but dont always know the full thing so i would like to search the database for items that contain what i put in the form in the serial number in the database.

any suggestions? i have tried 'like' but that doesnt work with part numbers.

Thanks

Rob

[oracleguy]

When you used 'like' did you include wildcards for it to search with?

It has been a while since I have done this but its like:
serialNumber LIKE '783A382%'

Where the % sign is the wildcard, I think that is the correct character.

[robojob]

not tried it, didnt know anything about it, i will investigate it using google. not sure how i would incorprate that with the form value from previous page though.... any ideas?

[robojob]

got it, the working query is:

SELECT * FROM table where serialno like '%"& request.form("serialnum") &"%';"

[Spudhead]

Just for reference, it varies slightly between databases:

Originally Posted by http://www.developerfusion.co.uk/show/674/

Visual Basic gives you several ways to provide pattern-matches in a
search string. Typically, you use pattern-matching characters for two
purposes: to search for strings in a VB application's GUI or variable,
or to look for items within a database, such as Access or SQL. When you
search for pattern matches in a database, however, depending on which
database you're using, different characters perform different matches.

The following list shows the different characters used by each database:

Required Match
Any single character
Access/VB: ? SQLServer: _
Zero or more characters
Access/VB: * SQLServer: %
Any single digit (0-9)
Access/VB: # SQLServer: n/a
Any single character in charlist
Access/VB: [charlist] SQLServer: [charlist]
Any single character not in charlist
Access/VB: [!charlist] SQLServer: [^charlist]