SQL 2005+

Basic T-SQL Queries

by Richard Carr, published at http://www.blackwasp.co.uk/SQLBasicQueries.aspx

The eighth part of the SQL Server Programming Fundamentals tutorial describes the use of the SELECT statement of the structured query language. This command allows the creation of database queries that return table rows that meet specified criteria.

Finding Values in a Range

Another interesting method for building a predicate is using the BETWEEN clause. This allows an inclusive range of values to be specified. When evaluated for a row, if the value appears within the range then the row is included in the results. The following statement retrieves all of the customers that were created within a specific date range.

SELECT
    FirstName,
    LastName,
    CreatedDate
FROM
    Customers
WHERE
    CreatedDate BETWEEN '2007-12-08' AND '2008-01-15'

NB: When using dates without specifying the time element, the time is assumed to be 12:00am. This means that in the previous query, if a customer were created on 15 January 2008 at any time other than midnight the row would be excluded from the results. To include such a row the end of the range should be '2008-01-15 23:59:59.997' or comparison operators should be used instead of BETWEEN.

Comparing Values Against a List

Sometimes you will want to determine if a value matches an item from a list. This is possible using the IN clause and a comma-separated list of values in parentheses. If we want to retrieve a list of the ID's of engineers that service the Humberside or Yorkshire areas, we can use the information in the EngineerWorkingAreas table and the following query:

SELECT DISTINCT
    EngineerId
FROM
    EngineerWorkingAreas
WHERE
    AreaCode IN ('HUM', 'NYO', 'SYO', 'WYO')

Performing Wildcard Searches

When working with character columns it is possible to perform pattern-matching queries using the LIKE keyword and a pattern containing wildcard characters and escape sequences. The column or expression to be evaluated is positioned to the left of the LIKE keyword and the pattern to be compared is placed to the right.

There are several wildcards that can be included in the pattern. The first is a percentage sign (%), which indicates that the value can include any number of any characters in place of the wildcard, including no characters at all. This is useful for "begins with", "ends with" or "contains" queries. For example, executing the following statement retrieves all customers with a last name that starts with the letter "M".

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    LastName LIKE 'M%'

The underscore (_) wildcard in a pattern is used to represent any single character. In the following example, four underscores are used to return customers with a last name that is exactly five characters long and ends with an "s".

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    LastName LIKE '____s'

Sometimes checking for any character in the pattern is not desired. Instead, you may wish to check that a particular character position contains a single character from a list. In these cases, you can use a pair of square brackets containing each acceptable character. When the predicate is evaluated it returns true only if the character at the position exists within the brackets. The following statement demonstrates this by finding customers with a first name of "Dita" or "Rita".

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    FirstName LIKE '[RD]ita'

The list of characters within the square brackets can be expressed as a range, rather than a discrete set. This is achieved by specifying the start and end of the range with a hyphen between the two extremes.

SELECT
    FirstName,
    LastName,
    BusinessName
FROM
    Customers
WHERE
    FirstName LIKE '[D-R]ita'

The list or range of values can also be negated using a caret symbol (^). In this case, the wildcard specifies that the character at the given position must not be within the stated list or range. In the following example engineers are included in the result set only if their name does not begin with any letter between "A" and "M".

SELECT
    EngineerName AS [Engineer Name],
    HourlyRate AS Cost
FROM
    Engineers
WHERE
    EngineerName LIKE '[^A-M]%'

One final use of the square bracket syntax is when you wish to find patterns that include one of the wildcard characters. To search for a string that includes a percentage symbol, for example, you would use a pattern such as "%[%]%". The outer percentage signs are wildcards but the inner one is interpreted as a literal character.