Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old March 14th, 2004, 12:42 PM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Query question

I would like to know how to make an SQL search to span 2 letters, this is what I have so far, but it only search litterally BETWEEN the letters, so 'a%' AND 'f%', only returns b%, c%, d% and e%... How can I change the string to allow the letters a AND f?

Select Case arrsearch(1)
    Case "a_f"
    query = "SELECT * FROM Recipe WHERE R_name BETWEEN 'a%' AND 'f%'"
    Case "g_l"
     query = "SELECT * FROM Recipe WHERE R_name BETWEEN 'g%' AND 'l%'"
    Case "m_r"
     query = "SELECT * FROM Recipe WHERE R_name BETWEEN 'm%' AND 'r%'"
    Case "s_z"
     query = "SELECT * FROM Recipe WHERE R_name BETWEEN 's%' AND 'z%'"


  #2 (permalink)  
Old March 14th, 2004, 03:40 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Since your queries are literal strings. You might this awkward construct (remember you're comparing literal 2 character strings alphabetically... probably not the most elegant solution):

Case "a_f"
    query = "SELECT * FROM Recipe WHERE R_name>='aa' AND R_name<='fz'"
Case "g_l"
    query = "SELECT * FROM Recipe WHERE R_name>='ga' AND R_name<='lz'"

Or something like this:

query = "SELECT * FROM Recipe WHERE (R_name LIKE 'a%') OR (R_name LIK 'b%') OR (R_name LIKE 'c%') OR (R_name LIKE 'd%') OR (R_name LIKE 'e%') OR (R_name LIKE 'f%')"

  #3 (permalink)  
Old March 16th, 2004, 12:33 AM
Friend of Wrox
 
Join Date: Mar 2004
Location: Ottawa, , Canada.
Posts: 139
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I'm intrigued by what you say, you say that how I did it is not elegant? How, if I may, would you have done it instead? Always interested in refining code

  #4 (permalink)  
Old March 17th, 2004, 04:26 PM
Authorized User
 
Join Date: Mar 2004
Location: , , .
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Why not use > and < instead of between ?

Case "a_f"
    query = "SELECT * FROM Recipe WHERE (R_Name>'A%' And R_Name<'F%')

??


  #5 (permalink)  
Old March 17th, 2004, 07:44 PM
Friend of Wrox
Points: 422, Level: 7
Points: 422, Level: 7 Points: 422, Level: 7 Points: 422, Level: 7
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jul 2003
Location: La Jolla, CA, USA.
Posts: 112
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:I'm intrigued by what you say, you say that how I did it is not elegant?
What I meant is that my solution is not the most elegant, but it does work.

Whereas in the comparison below, the % character is taken literally

Quote:
quote:Why not use > and < instead of between ?

Case "a_f"
    query = "SELECT * FROM Recipe WHERE (R_Name>'A%' And R_Name<'F%')
And since '%' comes before 'A', 'FA' or 'FB' would never be included in the result set.



Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Question Otacustes VB Databases Basics 6 July 15th, 2008 01:24 PM
sql query question ldp101068 SQL Server 2000 6 December 3rd, 2007 03:41 PM
Output Query to txt file from SQL Query everest SQL Server 2005 4 November 22nd, 2007 01:49 AM
SQL query question in Access dlamarche Access 3 April 3rd, 2005 10:42 PM
Sql query question. pankaj_daga SQL Language 3 November 30th, 2003 03:04 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.