Teradata database is not case sensitive (case specific), there may be times when we would like it to perform in a case sensitive manner. To accomplish this we use the CASESPECIFIC function.
SELECT Last_Name FROM Employee WHERE Last_Name = 'brown' (CASESPECIFIC);
*** Query completed. No rows found.
SELECT Last_Name FROM Employee WHERE Last_Name LIKE '%Ra%' (CASESPECIFIC);
last_name
-------------------
Ratzlaff
Rabbit
Note that "Crane" and "Trader" are also employees.
SELECT Last_Name FROM Employee WHERE POSITION(('Ra' (cs)) IN Last_Name) > 0;
last_name
--------------------
Ratzlaff
Rabbit
EXTRACT function:
The EXTRACT function can be used to return portions of a date or time value. It can not extract combinations like "year-month' or "month-day",
The result of the EXTRACT function is an integer value that represents that portion you are extracting
EXTRACT ( { YEAR } FROM date-value )
MONTH
DAY
HOUR
MINUTE
SECOND
SELECT EXTRACT(YEAR FROM DATE'2010-12-20' + 30) AS Yr,
EXTRACT(MONTH FROM DATE'2010-12-20' - 30) AS Mth,
EXTRACT(DAY FROM DATE'2010-12-20' + 30) AS Dy;
Yr Mth Dy
----------- ----------- -----------
2011 11 19
SELECT EXTRACT(HOUR FROM TIME'10:20:30') AS Hr,
EXTRACT(MINUTE FROM TIME'10:20:30') AS Mn,
EXTRACT(SECOND FROM TIME'10:20:30') AS Scd;
Hr Mn Scd
----------- ----------- -----------
10 20 30
TYPE Function
The TYPE is an attribute function that returns only the data type of a column or expression, i.e. it does not return result information.
SELECT TYPE(CHARACTER_LENGTH('Indiana Jones')); --> INTEGER
SELECT TYPE(-99999); --> INTEGER
SELECT TYPE(TRIM(-99999)); --> VARCHAR(11) (Left Justified)
SELECT TYPE(POSITION('x' IN 'ABCXYZ')); --> INTEGER
SELECT TYPE(POSITION('1' IN TRIM(2213))); --> INTEGER
SELECT TYPE(SUBSTRING('this text' FROM 6 FOR 4)); --> VARCHAR(4)
SELECT TYPE(SUBSTRING(12345 FROM 2)); --> VARCHAR(5) (Left Justified)
SELECT TYPE(ADD_MONTHS(DATE, 2)); --> DATE
SELECT TYPE(EXTRACT(DAY FROM DATE)); --> INTEGER
DEFAULT returns the default value for a column. This value is then treated as is any other projected value.
SELECT DEFAULT(c1) AS a,
SUBSTRING(DEFAULT(c2) FROM 3 FOR 2) AS b,
DEFAULT(c3) + c4 AS c,
DEFAULT(c3) + DEFAULT(c4) AS d
FROM abc;
The table must have rows of data for this to return a result. Hence, this table has one row.
a b c d
---------- -- ----------- -----------
Tuba oe 0
TITLE: returns the name of the title for a column. If it was not titled, it returns the column name. Assume that a column has this definition
Col1 INTEGER (TITLE 'Column 1')
Then this SELECT TITLE(Col1) will return "Column 1" as the columns new heading (which is only a title and not an name).
