SQL Functions

1
SQL Functions
2
Single-Row Functions

• Single-row functions return a single result row
  for every row of a queried table or view.
• These functions can appear in select lists, WHERE
  clauses and HAVING clauses.

3
Character Functions

• Character functions that return character values
  return values are of the same datatype as the
  input argument.
• Functions that return CHAR values are limited in
  length to 2000 bytes.
• Functions that return VARCHAR2 values are limited
  in length to 4000 bytes.
• If the length of the return value exceeds the
• limit, then Oracle truncates it and returns the
  result without an error message.

4
CHR

• CHR returns the binary equivalent of a character.
• The following example is run on an ASCII-based
  machine
• SELECT CHR(67)CHR(65)CHR(84) "Dog" FROM DUAL
• Dog
• ---
• CAT

5
GREATEST

• GREATEST returns the greatest of the list of
  expressions. All expressions after the first are
  implicitly converted to the data type of the
  first expression before the comparison.
• Examples
• SELECT GREATEST (HARRY, HARRIOT, HAROLD)
• "Greatest" FROM DUAL
• Greatest
• --------
• HARRY

6
LEAST

• Example
• The following statement is an example of using
  the LEAST function
• SELECT LEAST(HARRY,HARRIOT,HAROLD) "LEAST"
• FROM DUAL
• LEAST
• ------
• HAROLD

7
INITCAP

• Example
• The following example capitalizes each word in
  the string
• SELECT INITCAP(the soap) "Capitals" FROM DUAL
• Capitals
• ---------
• The Soap

8
INSTR

• The "in string" functions search string for
  substring.
• Example
• The following example searches the string
  "CORPORATE FLOOR", beginning with the third
  character, for the string "OR". It returns the
  position in CORPORATE FLOOR at which the second
  occurrence of "OR" begins
• SELECT INSTR(CORPORATE FLOOR,OR, 3, 2)
• "Instring" FROM DUAL
• Instring
• ----------
• 14

Start at
No. of Occurrence
9
LENGTH

• Returns the length of a string
• SELECT LENGTH(CANADIAN) "Length in characters"
• FROM DUAL
• Length in characters
• --------------------
• 8

10
LOWER

• LOWER returns char, with all letters lowercase.
• Examples
• SELECT LOWER(MR. SCOTT MCMILLAN) "Lowercase"
• FROM DUAL
• Lowercase
• --------------------
• mr. scott mcmillan

11
UPPER

• UPPER returns char, with all letters uppercase.
• The following example returns a string in
  uppercase
• SELECT UPPER(Large)
• FROM DUAL
• -----
• LARGE

12
LPAD

• LPAD returns string, left-padded to length n with
  the sequence of certain character. The following
  example left-pads a string with the characters
  "" and "."
• SELECT LPAD(Page 1,15,.) "LPAD example"
• FROM DUAL
• LPAD example
• ---------------
• ....Page 1

13
LTRIM

• LTRIM removes characters from the left of a
  string literal.
• Examples
• The following example trims all of the left-most
  xs and ys from a string
• SELECT LTRIM(xyxXxyLAST WORD,xy) "LTRIM
  example"
• FROM DUAL
• LTRIM example
• ------------
• XxyLAST WORD

14
RPAD

• The following example right-pads a name with the
  letters "ab" until it is 12 characters long
• SELECT RPAD(MORRISON,12,ab) "RPAD example"
• FROM DUAL
• RPAD example
• ----------------
• MORRISONabab

15
RTRIM

• The following example trims the letters "xy" from
  the right side of a string
• SELECT RTRIM(BROWNINGyxXxy,xy) "RTRIM
  example"
• FROM DUAL
• RTRIM examp
• -----------
• BROWNINGyxX

16
TO_CHAR(DATETIME)

• TO_CHAR (datetime) converts a date of type DATE,
  TIMESTAMP, , a value of type VARCHAR2 in a
  specified format.
• SELEEC birthdate, TO_CHAR(birthdate, YY-MM-DD)
• FORMATED
• FROM BIRTHDATE_TABLE
• WHERE LNAME WRITHT
• BIRHTDATE FORMATED
• ------------------ ------------------
• 20-MAY-49 49-05-20

17
TO_CHAR(NUMBER)

• TO_CHAR (number) converts a NUMBER datatype to a
  value of VARCHAR2
• Example
• The following statement uses implicit conversion
  to interpret a string and a number into a number
• SELECT TO_CHAR(01110 1) FROM dual
• TO_C
• ----
• 1111

18
SOUNDEX

• SOUNDEX returns a character string containing the
  phonetic representation of another string.
• Lets you compare words that are spelled
  differently, but sound alike in English.
• The following example returns the employees whose
  last names are a phonetic representation of
  "Smyth"
• SELECT last_name, first_name
• FROM hr.employees
• WHERE SOUNDEX(last_name) SOUNDEX(SMYTHE)
• LAST_NAME FIRST_NAME
• ---------- ----------
• Smith Lindsey
• Smith William

19
CONCAT

• CONCAT returns char1 concatenated with char2.
• Examples
• This example uses nesting to concatenate three
  character strings
• SELECT CONCAT(CONCAT(last_name, '''s job
  category is '),job_id) "Job"
• FROM employees
• WHERE employee_id 152
• Job
• -----------------------------
• Hall's job category is SA_REP

20
Datetime Functions

• Datetime functions operate on values of the DATE
  datatype. All datetime functions return a
  datetime or interval value of DATE datatype,
  except the MONTHS_BETWEEN function, which returns
  a number.

21
Character Functions Returning Number Values

• Character functions that return number values can
  take as their argument any character data type.
• The character functions that return number values
  are
• ASCII
• INSTR
• LENGTH

22
Some Datetime functions

• ADD_MONTHS
• LAST_DAY
• MONTHS_BETWEEN
• NEXT_DAY
• ROUND (date)
• SYSDATE
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TRUNC (date)

23
TO_DATE

• TO_DATE converts char of CHAR, VARCHAR2, NCHAR,
  or NVARCHAR2 datatype to a value of DATE datatype
  in a specified format.
• The following example converts a character string
  into a date
• SELECT TO_DATE(January 15, 1989, 1100 A.M.,
• Month dd, YYYY, HHMI A.M.)
• FROM DUAL
• TO_DATE(
• ---------
• 15-JAN-89

24
TO_TIMESTAMP

• Examples
• The following example converts a character string
  to a timestamp
• SELECT TO_TIMESTAMP (1999-12-01 110000,
  YYYY-MM-DD HHMISS)
• FROM DUAL
• TO_TIMESTAMP(1999-12-01110000,YYYY-MM-DDHHM
  ISS)
• --------------------------
• 01-DEC-99 11.00.00.00 AM

25
Add_Months

• ADD_MONTHS returns the date d plus n months. The
  argument n can be any integer.
• Examples
• The following example returns the month after the
  hire_date in the sample table employees
• SELECT TO_CHAR(
• ADD_MONTHS(hire_date,1),
• DD-MON-YYYY) "Next month"
• FROM employees
• WHERE last_name Baer

26
MONTHS_BETWEEN

• MONTHS_BETWEEN returns number of months between
  two.
• Examples
• The following example calculates the months
  between two dates
• SELECT MONTHS_BETWEEN
• (TO_DATE(02-02-1995,MM-DD-YYYY),
• TO_DATE(01-01-1995,MM-DD-YYYY) ) "Months"
• FROM DUAL
• Months
• ---------
• 1.03225806

27
SYSDATE

• SYSDATE returns the current date and time. The
  datatype of the returned value is DATE. The
  function requires no arguments. In distributed
  SQL statements, this function returns the date
  and time on your local database.
• The following example returns the current date
  and time
• SELECT TO_CHAR
• (SYSDATE, MM-DD-YYYY HH24MISS)"NOW"
• FROM DUAL
• NOW
• -------------------
• 04-13-2001 094551