Suppose you have a big list of values and they are in a chronological order than in that case it would be easier to provide a range of values than typing in the whole list. At this place the operator ’BETWEEN AND’ comes into picture.  If you have character values which are similar in nature, like starting with a pattern or ending with a pattern or there is a pattern in the middle, you can make use the LIKE operator to narrow your result set. Here are the syntaxes;

SELECT {*|<Col1>}, <Col2>, <Col3>…

FROM <table name>

WHERE <column name> BETWEEN <lowest values> AND <highest value>

SELECT {*|<Col1>}, <Col2>, <Col3>…

FROM <table name>

WHERE <column name> LIKE [ALL | ANY] ‘[% | _] character string [% | _]’

To use of LIKE operator effectively, you have to provide the right pattern to it and never forget to use the ‘single’ quotes. One good thing about the LIKE operator in Teradata SQL is that we can pass multiple values (pattern) for our search using ALL or ANY operator, which I do not think is present in any other database environment. Here is any example;

SELECT First_Name, Last_Name

FROM employee

WHERE Last_Name LIKE ANY (‘%Sim%’,’ %Pet%’)

The above would return rows if either of the matching pattern found

SELECT First_Name, Last_Name

FROM employee

WHERE Last_Name LIKE ALL (‘%Sim%’,’ %Pet%’)

The above would return rows only if all of the matching pattern found. If you have an underscore present in your data value and you want that to come in your result set than you have to the escape operator to include the underscore as a value and not as a wildcard character. Here is the way

SELECT First_Name, Last_Name

FROM employee

WHERE Last_Name LIKE (‘%S\_%’) ESCAPE ‘\’ 

The above query would find results like S_123, S_mon, s_TUE, s_533T etc. Note that the underscore is present at the 2nd position of the string.

Our Random Articles

More Links