Home

Topics on Filtering Records

 

Other Topics on Filtering

 

Hiding a Column

In our SELECT statements so far, we were selecting the columns we needed to display. When formulating such a statement, you can apply a condition to a column without including that column in the result.

For example, consider a query used to display a list of female students. Since we know that the result would show only the girls, it becomes redundant to include the Gender column in the statement. In this case, you can hide that column in the result.

To visually hide a column from a query, in the Criteria pane, set the condition expression in its corresponding Filter box. Then click the check box of Outpu to remove it.

To hide a field in SQL, omit that column in the SELECT statement but involve it in the WHERE condition. Here is an example:

SELECT DateOfBirth, LastName,
       FirstName, State, ParentsNames
FROM   Registration.Students
WHERE Gender = N'Female';
GO

This would produce:

Topics on Filtering Records
 

WHERE

Notice that the SELECT statement doesn't have the Gender column and the resulting query doesn't show the Gender column.

Practical LearningPractical Learning: Not Showing a Column

  1. Start the computer and log in
  2. Launch Microsoft SQL Server and click Connect
  3. In the Object Explorer, expand Databases. Make sure you have the FunDS1 database created in the previous lesson (if you don't have it, create it by opening the FunDS1.sql file (FunDS1.sql) and executing it)
  4. In the Object Explorer, expand FunDS1 and expand Tables
  5. Right-click Inventory.StoreItems and click Edit Top 200 Rows
  6. Right-click the table in the middle window, position the mouse on Pane, and click Diagram
  7. Right-click in Diagram window -> Pane -> Criteria
  8. Right-click in Diagram window -> Pane -> SQL
  9. In the Diagram pane, remove the check boxes of Size and DiscountRate
  10. In the SQL pane, delete TOP (200)
  11. In the Criteria pane, click the first combox box under Alias and type Item #
  12. Click the second box under Alias and type Date Entered
  13. Click the fourth box under Alias and type Name/Description
  14. Click the fifth box under Alias and type Unit Price
     
    Output
  15. Right-click in the Diagram pane and click Execute SQL
  16. To see the items made by Kenneth Cole, in the Criteria pane, click the box at the intersecion of Manufacturer and Filter
  17. Type Kenneth Cole
  18. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
  19. To hide the column, in the Criteria pane, click the check box at the intersection of Manufacturer and Output
     
    Output
  20. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Hiding a Field

Selecting Into a Table

Consider the following Employees table:

CREATE DATABASE Corporation;
GO

USE Corporation;
GO
CREATE TABLE Employees
(
	EmployeeNumber int unique not null,
	FirstName nvarchar(20),
	LastName nvarchar(20) not null,
	Department nvarchar(50) null,
	EmploymentStatus nvarchar(30),
	HourlySalary money
);GO
INSERT INTO Employees
VALUES(84628, N'Anthony', N'Holms', N'Corporate', N'Full Time', 24.72),
      (40574, N'William', N'Wilson', N'Information Technology', N'Full Time', 21.18),
      (27462, N'Peter', N'Swanson', N'Corporate', N'Full Time', 22.84),
      (52835, N'Edward', N'Johansen', N'Information Technology', N'Consultant', 15.50),
      (93075, N'Sharon', N'Edson', N'Accounting', N'Full Time', 28.74),
      (82718, N'Yanuh', N'Hadhah', N'Corporate', N'Full Time', 14.86),
      (29174, N'Hamin', N'Souleyman', N'Public Relations', N'Consultant', 18.76);
GO

You can use all or some records from an existing table to create a new table that would contain those existing records. To do this, use the following formula:

SELECT Columns INTO NewTableName FROM ExistingTable [WHERE Condition]

To use all columns and all records, start with the SELECT operator, followed by *, followed by INTO, followed by a name for the table you want to create, followed by FROM, and the name of the original table that contains the records. Here is an example:

USE Corporation;
GO
SELECT * INTO CompanyRecipients FROM Employees;
GO

Instead of using all columns, you can specify only the desired columns after the SELECT keyword. Here is an example:

USE Corporation;
GO
SELECT EmployeeNumber, LastName, FirstName, EmploymentStatus
INTO Salaried FROM Employees;
GO

Instead of using all records, you can use a condition by which the records would be selected and added to the new table you are creating. To set the condition, you can create a WHERE statement as the last in the whole expression. Here is an example:

USE Corporation;
GO
SELECT *
INTO FullTimeEmployees
FROM Employees
WHERE EmploymentStatus = N'Full Time';
GO

Filtering by a Pattern

 

Introduction

Most or all of the criteria we have specified with the WHERE keyword had to exactly match the specified criterion. In some cases, you may not remember the exact value of records but you want to specify some type of approximation. To do this, you use the LIKE operator.

If you are visually creating the statement, in the Critera pane, in the box corresponding to Filter for the column on which the condition would be applied, type the LIKE condition.

In Transact-SQL, the LIKE operator is used in a formula as follows:

Expression LIKE pattern

The Expression is what will be evaluated. It must be a clear and valid expression. It is usually the name of a column. The pattern can be a value to be found in the Expression column.

In most cases, the Expression is preceded by WHERE. The operation performed by LIKE deals with string-based columns. The LIKE operator is used to perform a comparison to find out if the value of a field (the Expression) exactly or approximately matches a pattern. If the operation is valid, it produces a Boolean value as True or False. You can then use that result as you see fit.

Exactly Matching a Value

The most fundamental comparison performed on a string-based field is to find out whether it is equal to a certain string. We know that this operation can be performed using the = operator. Here is an example:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  State = N'VA';
GO

The LIKE operator can be used to perform the same operation. To do this, use the following formula:

WHERE ColumnName LIKE Value

In this case, the name of the column is used as the Expression and the string in the field is the pattern. Here is an example:

SELECT FirstName, LastName,
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  State LIKE N'VA';
GO

This and the previous code produce the same result:

LIKE

To make the operation easier to read, you can put it in parentheses. The above code can be written as follows:

SELECT FirstName, LastName,
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE (State LIKE N'VA');
GO

Practical LearningPractical Learning: Introducing Patterns

  1. In the Criteria pane, click the check box of the Output column for the Manufacturer field
  2. Press Tab three times to select the value in the Filter column
  3. Type LIKE Tommy Hilfiger
     
    Introducing Patterns
  4. To see the result, on the main menu, click Query Designer -> Execute SQL
     
    Introducing Patterns

Negating a Pattern

As done with all Boolean operations, there are ways you can negate a LIKE operation. Probably the easiest way to negate a LIKE operation is to precede the Expression with the NOT operator. Here is an example:

SELECT FirstName, LastName,
       Gender, State, ParentsNames
FROM   Registration.Students
WHERE NOT State LIKE N'VA';
GO

If you precede the expression with NOT, it is recommended that you put the section after NOT between parentheses. Here is an example:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE NOT (State LIKE N'VA');
GO

As an alternative, in most cases, you can also precede the LIKE keyword with NOT. Here is an example:

LIKE NOT

This formula would produce the same result as the previous one:

LIKE

You can also negate a negation. Here is an example:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT State NOT LIKE N'VA';
GO

This is the same as:

SELECT FirstName, LastName, 
       Gender, [State], ParentsNames
FROM   Registration.Students
WHERE  NOT (State NOT LIKE N'VA');
GO

If you negate a negation, you can get the result as you none of both negations was used.

Practical LearningPractical Learning: Negating a Pattern

  1. In the SQL pane, click the right side of WHERE, press the Space bar, and type not
  2. To see the result, on the main menu, click Query Designer -> Execute SQL
  3. In the Criteria pane, click Manufacturer, press Tab six times and type LIKE Ralph Lauren
  4. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Introducing Patterns
  5. In the Criteria pane, click Manufacturer, press Tab six times and press Delete

Matching Any Character

The idea of using a LIKE operator is to give an approximation of the value you want to compare to a field. To formulate this approximation, you use wildcards combined with the LIKE operator.

If you want to match any character, in any combination, for any length, use the % wildcard. If you precede it with a letter, as in S%, the condition would consist of finding any string that starts with S. Imagine that you want to get a list of students whose last names start with S. You would type the condition as LIKE 'S%'. To do this visually, in the Criteria pane, under the Filter column, type the condition. Here is an example:

LIKE Any Character

This would produce:

LIKE

You can negate this condition by preceding it with NOT. Here is an example:

SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  NOT (LastName LIKE N'S%')

This would produce:

LIKE

This time, the result is the list of students whose last names don't start with S.

 
 
 

Practical LearningPractical Learning: Matching Any Character

  1. To see the manufacturers whose name ends with e, in the Criteria pane, while the caret is in the Filter corresponding to the Manufacturer, type like %e
  2. To see the result, on the main menu, click Query Designer -> Execute SQL
     
    Matching Any Character

Matching a Sub-String

When you precede the % character with a letter, only that letter would be considered. Alternatively, you can specify a group of characters, called a sub-string, that would precede the % symbol. For example, if you have some first names that start with Ch in a list but you don't remember the end of the name you are looking for, to create the list, you can specify that the first name would start with Ch and end with whatever. In this case, you would use Ch% as follows:

SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  LastName LIKE N'Ch%'

This would produce:

LIKE

Instead of ending a letter or a group of letters with %, you can begin the LIKE statement with %. An example would be LIKE "%son". In this case, all strings that end with son, such as Johnson or Colson, would be considered.

If you remember neither the beginning nor the end of a string you want to search for, but you know a sub-string that is probably included in the type of string you are looking for, you can precede it with % and end it with %. An example would be LIKE "%an%". In this case, all strings that include "an" anywhere inside would be considered. Here is an example:

SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  LastName LIKE '%an%'

This would produce:

LIKE

Like the other SQL statements, you can also negate this one using the NOT operator.

Practical LearningPractical Learning: Matching a Sub-String

  1. In the Criteria pane, click Manufacturer, press Tab six times and press Delete
  2. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
  3. Scroll down in the Results pane and notice many names containing Ralph Lauren.
    In the Criteria pane, while the caret is blinking, type LIKE %Lauren%
     
    Matching a Sub-String
  4. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Matching a Sub-String
  5. In the Criteria pane, click Manufacturer, press Tab six times and press Delete
  6. Press the down arrow key and type LIKE %dress%
  7. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Matching a Sub-String

Matching a Range of Characters

The % wildcard is used to precede or succeed a specific character or a sub-string. If you want to consider only a range of characters from the alphabet, you can include the range in square brackets. To do this, type [, followed by the lowest character of the range, followed by -, followed by the highest character of the range, followed by ]. For example, to consider the range of letters between p and s, you would use '[p-s]'. Then, either to the left, to the right, or to both sides of this expression, type % to specify whether to include any character or combination of characters before or after the expression. Here is an example:

SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  LastName LIKE N'%[p-s]'

In this case, the result would be a list of students whose last names end with p, q, r, or s. This would produce:

LIKE

Notice that the list includes only the students whose last names end with a letter between p and s.

Not Ending With a Range of Characters

As opposed to considering the characters that are in a specific range, to specify a character or a range of characters that must NOT be considered, use the ^ character inside the square brackets but before the desired range. Here is an example:

SELECT FirstName, LastName, Gender, SingleParentHome
FROM   Registration.Students
WHERE  (LastName LIKE N'%[^p-r]')

The result would be a list of students whose last name end with a letter other than p, q, r, or s.

Once again, remember that you can negate an expression by preceding it with NOT. Note that if you negate an expression that include ^, you would get the same result as not using ^.

Practical LearningPractical Learning: Resuming the Lessons

  1. To see a list of items made by Kenneth Cole, in the Criteria pane, replace Lauren with LIKE %kenneth cole%
  2. To see the list of items in alphabetical order, in the Criteria pane, click the box at the intersection of the ItemName and Sort Type
  3. Select Ascending
  4. To see the result, on the Query Designer toolbar, click the Execute SQL button Execute SQL
     
    Matching a Sub-String
  5. Close the Query Designer
  6. Close Microsoft SQL Server

Exercises

   

Lesson Summary Questions

  1. What keywords can be used to create a condition for data selection (Choose two)?
    1. SELECT
    2. WHERE
    3. ADD
    4. LIKE
    5. FROM
  2. What wildcard is used to match any number of characters?
    1. %
    2. @
    3. #
    4. ^
    5. !
  3. What wildcard character is used to match any single character?
    1. %
    2. @
    3. _
    4. ^
    5. !
  4. What symbols are used to delimit a range of characters in a pattern?
    1. ()
    2. {}
    3. <>
    4. ||
    5. []
    6. ::
  5. What character is used to match any single character outside of a specified range?
    1. %
    2. @
    3. _
    4. ^
    5. !

Answers

  1. Answers
    1. Wrong
    2. Right: Among the words used to formulate conditions are WHERE and LIKE
    3. Wrong:
    4. Right: Among the words used to formulate conditions are WHERE and LIKE
    5. Wrong:
  2. Answers
    1. Right Answer: The % wildcard can be be used before or after a character, a sub-string, or a string
    2. Wrong Anwer: @ is not a valid wildcard in a LIKE statement of Transact-SQL
    3. Wrong Answer: # is not a wildcard
    4. Wrong Answer: The ^ wildcard is not used to match a character
    5. Wrong Answer: ! is not used as a wildcard in Transact-SQL
  3. Answers
    1. Wrong Anwer
    2. Wrong Answer
    3. Right Answer: The underscore "_" is used to match any single character
    4. Wrong Answer
    5. Wrong Answer
  4. Answers
    1. Wrong Anwer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: The square brackets are used to delimit a pattern to match
    5. Wrong Answer
  5. Answers
    1. Wrong Anwer
    2. Wrong Answer
    3. Wrong Answer
    4. Right Answer: The square brackets are used to delimit a pattern to match
    5. Wrong Answer
 
 
   
 

Previous Copyright © 2007-2013, FunctionX, Inc. Next