Wildcard Characters Available with LIKE in Visual Studio .NET

Paint QR Code JIS X 0510 in Visual Studio .NET Wildcard Characters Available with LIKE

Table 4-2 Wildcard Characters Available with LIKE
QR Code 2d Barcode Printer In Visual Studio .NET
Using Barcode generation for Reporting Service Control to generate, create QR Code JIS X 0510 image in Reporting Service applications.
Painting Barcode In VS .NET
Using Barcode drawer for Reporting Service Control to generate, create barcode image in Reporting Service applications.
An example of using LIKE to identify any contacts whose last name starts with St is shown next Notice that the entire comparison string, including the wildcard, is enclosed in single quotes
QR Code ISO/IEC18004 Creator In C#.NET
Using Barcode generator for .NET Control to generate, create QR Code 2d barcode image in .NET applications.
QR Code Drawer In .NET
Using Barcode encoder for ASP.NET Control to generate, create QR Code JIS X 0510 image in ASP.NET applications.
SELECT Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE LastName LIKE 'St%'
QR Code 2d Barcode Creator In .NET
Using Barcode generator for VS .NET Control to generate, create QR Code image in .NET framework applications.
QR Code Generator In VB.NET
Using Barcode drawer for .NET framework Control to generate, create Quick Response Code image in .NET framework applications.
4: Transact-SQL
Barcode Encoder In .NET Framework
Using Barcode encoder for Reporting Service Control to generate, create bar code image in Reporting Service applications.
Draw Code 128B In .NET
Using Barcode drawer for Reporting Service Control to generate, create Code 128 Code Set A image in Reporting Service applications.
Similarly, we could search for any name that ends with st and then any other single letter with the following script:
Make Code 3 Of 9 In VS .NET
Using Barcode generator for Reporting Service Control to generate, create USS Code 39 image in Reporting Service applications.
Bar Code Creation In Visual Studio .NET
Using Barcode creation for Reporting Service Control to generate, create bar code image in Reporting Service applications.
SELECT Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE LastName LIKE '%st_'
EAN 13 Generator In .NET
Using Barcode generator for Reporting Service Control to generate, create GS1 - 13 image in Reporting Service applications.
Universal Product Code Version A Generator In .NET
Using Barcode creator for Reporting Service Control to generate, create UPCA image in Reporting Service applications.
It s also possible to embed functions within queries Let s say our database is set with a casesensitive (CS) collation and we re looking for any contacts with a last name starting with Mc whether they ve been entered as MC, Mc, or even mc We could use the UPPER function that converts a string to all uppercase and then compare it with the LIKE predicate:
USPS POSTNET Barcode Printer In VS .NET
Using Barcode maker for Reporting Service Control to generate, create Delivery Point Barcode (DPBC) image in Reporting Service applications.
Creating UCC - 12 In .NET
Using Barcode encoder for Visual Studio .NET Control to generate, create UCC - 12 image in .NET applications.
USE AdventureWorks SELECT Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE UPPER(LastName) LIKE 'MC%'
Encoding Linear Barcode In VB.NET
Using Barcode maker for VS .NET Control to generate, create Linear image in .NET framework applications.
EAN-13 Supplement 5 Decoder In Java
Using Barcode reader for Java Control to read, scan read, scan image in Java applications.
Logical Operators (AND, OR, NOT)
GTIN - 128 Reader In Visual C#.NET
Using Barcode recognizer for .NET Control to read, scan read, scan image in VS .NET applications.
USS Code 128 Printer In Visual Studio .NET
Using Barcode generation for ASP.NET Control to generate, create Code128 image in ASP.NET applications.
Logical operators allow us to combine multiple expressions We can use AND to meet multiple conditions, or we can use OR to meet one of multiple conditions The following script searches for contacts with the last name of Smith OR the first name of John and returns about 160 rows:
Bar Code Maker In VS .NET
Using Barcode drawer for VS .NET Control to generate, create barcode image in VS .NET applications.
Bar Code Creation In None
Using Barcode generator for Font Control to generate, create bar code image in Font applications.
SELECT Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE LastName = 'Smith' OR FirstName ='John'
Similarly the following script searches for contacts with the last name of Smith AND the first name of John and returns only a single row:
SELECT Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE LastName = 'Smith' AND FirstName ='John'
Ranges (BETWEEN and NOT BETWEEN)
BETWEEN allows us to simplify the use of comparison operators The biggest thing to remember about BETWEEN is that it is inclusive In other words, if we are searching for a value between 5 and 10, it includes the values 5 and 10 We can replicate the same search using comparison operators and logical operators For example, BETWEEN 5 and 10 is the same as Value >=5 AND Value <=10 The scripts that follow demonstrate both examples:
SELECT ContactID, Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE ContactID BETWEEN 5 and 10 SELECT ContactID, Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE ContactID >=5 AND ContactID <=10
MCITP SQL Server 2005 Database Developer All-in-One Exam Guide
Lists of Values (IN and NOT IN)
Using a list of values, we can greatly simplify complex OR statements For example, say you are asked to retrieve a listing of contacts with first names of Sandy, Jay, Carla, or Ronald Using the OR statement, your query may look like this:
SELECT ContactID, Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE FirstName = 'Sandy' OR FirstName = 'Jay' OR FirstName = 'Carla' OR FirstName = 'Ronald'
Instead, we can simplify this query with the IN operator by using the following query:
SELECT ContactID, Title, FirstName, LastName, EmailAddress FROM PersonContact WHERE FirstName IN ('Sandy', 'Jay', 'Carla','Ronald')
The query optimizer would handle both statements (using OR or IN) the same way However, for a DBA analyzing queries, the query using IN is much easier to read
Unknown Values (IS NULL and IS NOT NULL)
Checking for NULL values within a query is sometimes required For example, you might be asked to provide a listing of customers that have a phone number, or only customers that do not have phone numbers A common mistake when checking for null values is to try to use a comparison operator such as = While the = comparison operator can be used to check for known values, the value of NULL is unknown, so a comparison operator will not work Instead, we must use the IS NULL or IS NOT NULL comparison operators The following query can be executed against AdventureWorks and will check for any contacts in the PersonContact table that don t have middle names listed (or in other words, that have a value of NULL for their middle name):
SELECT ContactID, Title, FirstName, MiddleName LastName, EmailAddress FROM PersonContact Where MiddleName IS Null
EXAM TIP Remember that we must use the IS NULL or IS NOT NULL function to check for NULL valuesWe cannot use a comparison to an empty string to determine if a value is NULL The ISNULL Function The ISNULL function is a handy function you should be aware of when working with NULL values It allows you to query NULL data, but instead of outputting the value of NULL, you can replace NULL with a specified replacement value This can be especially useful if you need to perform some mathematical calculation on the data such as SUM or AVG The basic syntax is:
ISNULL ( check_expression , replacement_value )
In the following exercise, we ll create a table in the AdventureWorks database, modify it slightly, and view the differences in the data depending on whether we re looking at the data as NULL or if we are giving it a value of zero
Copyright © OnBarcode.com . All rights reserved.