SQL Select Statement

[mozingod]

How can I get partial matches from a SELECT statement? Like if the user enters 9004 for part number 90040912, how can I get it to return that? Isn't it something like "SELECT * FROM table LIKE '9004'", or am I way off? Thanks. Darrell Mozingo

 

[Tarwn]

Your are correct, except you will need to put some wildcard in there to let the server know where it is allowed to make matches. In this case you will want a wildcard at the end of the string:
SELECT * FROM table LIKE '9004%'
If you wanted to match 11900411 Then you would use
SELECT * FROM table LIKE '%9004%'

Note: If you are using MS Access you will need to use * for the wildcard instead of %.

Additional Note: % means match 0 or more of any character in that position, you can also use _ (SQL Server) or ? (Access) to match match only a single character. Thus '_9004' would match 19004 but not 119004.

Hope that helps,
-Tarwn


--- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[mozingod]

Yes, very much so. Thanks!! Darrell Mozingo

 

[mozingod]

How would I use that with a WITH statement? "SELECT * FROM table WHERE field = '%9004%'"? Darrell Mozingo

 

[mozingod]

Never mind, I got it... Darrell Mozingo

 

[ggs54]

I have an ASP script, which pulls user input from a form and I need to query my Access 2000 database. I'm using FrontPage 2000 for development and my site server has the FrontPage Extensions installed.

Here's my question. Are you limited to a single SQL query to return a recordset or is it possible to sequence multiple SQL statements that will query the results of the preceeding statement. I'm dealing with plant material and my form has 22 different search possibilites such as botanic name, height, color, dry soil, etc. The ASP manual that I'm using implies that only one SQL statement can be used and that all of the search criteria needs to be contained in that single statement. The format described, which I have used successfully on other projects, is as follows:

Dim SQLQuery
SQLQuery = " a whole boatload of AND's OR's LIKE's for 22 search components "

objRS.Open SQLQuery, Connection, ...

What I have in mind, and I have no idea if it's possible, is this:

Dim SQLQuery
SQLQuery = "component 1 criteria"

objRS.Open SQLQuery, Connection, ...

SELECT * FROM objRS Where component 2 criteria
SELECT * FROM objRS Where component 3 criteria

and so on.

Can this be done or am I experiencing wishful thinking?

Thanks for your help.

 

[GaryC123]

Ah well post it here as well as experts-exchange

In the search context are they requesting a search on all possibilities e.g. botanic name and height and color etc or just one area e.g botanic name

http://www.intouch-media.co.uk

 

[whloo]

the way to solve this problem shld be using sub query. example will be....
select * from table1 where plantName in (select plantName from table2 where....)
hope this will help