Social Icons

Operators

TeradataWiki-Teradata Operators
Operators are symbols or words that cause an 'operation' to occur on one or more elements called 'operands'. Below are the types of Operators
=  
<> 
>  
<  
>= 
<= 
BETWEEN AND
[NOT] IN
IS [NOT] NULL
EXISTS
LIKE

In this section will cover....

BETWEEN Operator:
  • To locate rows for which a numeric column is within a range of values.
Example:

Select the name and the employee's manager number for all employees whose job codes are in the 430000 range.

SELECT    first_name
            ,last_name
            ,manager_employee_number
FROM      employee
WHERE     job_code BETWEEN 430000 AND 439999;

first_name last_name manager_employee_number
---------- ---------- ------------------------         
Loretta Ryan                 801                                  
Armando Villegas         1005                                
  • To locate rows for which a character column is within a range of values.
SELECT last_name
FROM employee
WHERE last_name BETWEEN 'r' AND 's';

last_name 
------------
Ryan        

IN Operator:
Use the IN operator as shorthand when multiple values are to be tested.
Ex:Select the name and department for all employees in either department 401 or 403.

SELECT    first_name
            ,last_name
            ,department_number
  FROM      employee
  WHERE     department_number IN (401, 403);

first_name last_name department_number
----------- ----------- -----------------------
Darlene Johnson 401               
Loretta Ryan 403               
Armando Villegas 403               
James Trader 401               

NOT in Operator:
Use the NOT IN operator to locate rows for which a column does not match any of a set of values

SELECT    first_name
            ,last_name
            ,department_number
 FROM      employee
 WHERE     department_number NOT IN (401, 403) ;

first_name last_name department_number
----------- ----------- ---------------------- 
Carol Kanieski 301                       
John Stein 301                       


LIKE operator:
The LIKE operator searches for patterns matching character data strings.

Here are some examples using the LIKE operator:

String pattern example: Meaning:
LIKE 'JO%' begins with 'JO'
LIKE '%JO%' contains 'JO' anywhere
LIKE '__HN' contains 'HN' in 3rd and 4th position
LIKE '%H_' contains 'H' in next to last position

Below are the examples

Display the full name of employees whose last name contains the letter "r" followed by the letter "a".

  SELECT    first_name
                   ,last_name
  FROM      employee
  WHERE     last_name LIKE '%ra%';

first_name                      last_name       
------------------------------  ---------------
James                           Trader              
Peter                           Rabbit               
I.B.                            Trainer              
Robert                          Crane              
Larry                           Ratzlaff            

Note: LIKE Operator -- Case-Sensitive Comparison

Display the full name of employees whose last name contains "Ra". This is a case-sensitive test.

   SELECT    first_name
                   ,last_name
  FROM       employee
  WHERE     last_name (CASESPECIFIC) LIKE '%Ra%';

first_name                      last_name             
------------------------------  --------------------
Peter                           Rabbit                     
Larry                           Ratzlaff                  

The default comparison for Teradata mode is not case-specific.

Use of the Teradata extension (CASESPECIFIC) forces a case-specific comparison.

LIKE Operator -- Using Quantifiers

There are three such quantifiers:

ANY — any single condition must be met (OR logic)
SOME — same as ANY
ALL — all conditions must be met (AND logic)

Display the full name of all employees with both "E" and "S" in their last name.

  SELECT   first_name
                 ,last_name
  FROM      employee
  WHERE     last_name LIKE ALL ('%E%', '%S%');

first_name                      last_name             
------------------------------  ------------------- 
John Stein                 
Carol Kanieski            
Arnando Villegas            

Display the full name of all employees with either an "E" or "S" in their last name.

 SELECT    first_name
            ,last_name
  FROM      employee
  WHERE     last_name LIKE ANY ('%E%', '%S%');

first_name                      last_name             
------------------------------  --------------------
John Stein                 
Carol Kanieski            
Arnando Villegas             
Darlene Johnson             
James Trader