6.3 Use Wildcards and Ranges of Values in a SQL Query
I need to be able to either search for a range of values, or at least be able to use wild cards
with my query. How do I do this using T-SQL?
Technique
This is one of those fairly simple but necessary How-Tos. You will learn how to use a
combination of both wild cards and a range of values. Here is the T-SQL routine that you
will use for this How-To:
SELECT Customers.CompanyName, Orders.OrderID, Orders.OrderDate FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
WHERE Customers.CustomerID LIKE 'A%'
AND Orders.OrderDate BETWEEN '11/01/1996' AND '12/01/1996'
Note
The literal values have been used here, rather than the text box values
that will be used in the How-To.

Using Wild Cards
Fairly similar to the wild cards of the old DOS days, wild cards in T-SQL are fairly
straightforward to use. It is just a matter of knowing which one to use for which task.
When using wild cards, you will use the LIKE operator, as seen in the SQL string for this
How-To.
You can see where the LIKE operator is used with A%. The % is used to specify
anything after the given letter. In this case, it's used for anything starting with the letter A.
(This operator will, of course, have to have the OrderDate fall between the dates
specified, but we'll talk about this in a moment. You can use other wild cards as well,
such as the following:
•
% (Percent sign). You use this to specify any given group of characters. If used
before a letter or group of letters, you are then specifying that you want values
ending with those letters. For instance, if you specify %ing, you get skiing, flying,
and so on.
•

Text A%
Label Text Order Date: From
Label Text To
TextBox Name txtFromDate
Text 11/01/1996
TextBox Name txtToDate
Text 12/01/1996
Label Text SQL String
Label Name lblSQLString
Label Text Results
DataGrid Name dgResults
2. Add the following code in Listing 6.6 to the Load event of the form. (Double-click
on the form to bring up the code.)
Listing 6.6 frmHowTo6_3.vb: Calling GenerateData When Loading the Form
Private Sub frmHowTo6_3_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load

GenerateData()

End Sub
3. In the class module for the form, add the code in Listing 6.7 to create the
GenerateData routine. After creating the SQL statement, this routine assigns it to
the Text property of lblSQLString. Then the string is used in a data adapter that
was created to fill the dtResults data table. Last, the data table is set as the data
source for dgResults.
Listing 6.7 frmHowTo6_3.vb: Generating Data Using LIKE and BETWEEN
Statements
Sub GenerateData()

'-- Build the SQL String

4. Add the code in Listing 6.8 to the TextChanged events of txtCustomerID,
txtFromDate, and txtToDate, respectively. These routines call GenerateDate when
the values change.
Listing 6.8 frmHowTo6_3.vb: Calling the GenerateData Routine When Text
Is Updated
Private Sub txtCustomerID_TextChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles txtCustomerID.TextChanged
GenerateData()
End Sub

Private Sub txtFromDate_TextChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles txtFromDate.TextChanged
GenerateData()
End Sub

Private Sub txtToDate_TextChanged(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles txtToDate.TextChanged
GenerateData()
End Sub
Figure 6.4. A common problem with inner joins is retrieving multiple records when
you just want to see one per occurrence.

Comments
By placing your use of wild cards and allowing for ranges of values, you can make your
applications and the querying of data more versatile than ever!