Oracle 11g: SQL - Single-Row Functions

Objectives

  • Use functions to change the case of field values and strings: UPPER, LOWER, INITCAP.
  • Manipulate substrings with SUBSTR and INSTR.
  • Nest functions.
  • Determine string length using LENGTH.
  • Pad strings to a certain width using LPAD and RPAD.
  • Remove specific characters using LTRIM and RTRIM.
  • Substitute strings with REPLACE and TRANSLATE.
  • Round and truncate numeric data using ROUND and TRUNC.
  • Return the remainder of division using MOD.
  • Use ABS to set numeric values as positive.
  • Raise a number to a power using the POWER function.
  • Calculate months between two dates using MONTHS_BETWEEN.
  • Manipulate dates using ADD_MONTHS, NEXT_DAY, LAST_DAY, and TO_DATE.
  • Differentiate between CURRENT_DATE and SYSDATE.
  • Extend pattern matching with regular expressions.
  • Correct problems with NULL values using NVL.
  • Display dates and numbers in a specific format with TO_CHAR.
  • Perform condition processing similar to an IF statement with DECODE.
  • Use the SOUNDEX function to identify character phonetics.
  • Convert strings to numeric with TO_NUMBER.
  • Use the DUAL table to test functions.

Terminology

  • Function: A predefined code block that accepts arguments.
  • Single-row function: Returns one row of results for each record processed.
  • Multiple-row function: Returns one result per group of data processed.

Types of Functions

  • Case conversion functions: UPPER, LOWER, INITCAP.
  • Character manipulation: SUBSTR, INSTR, LENGTH, LPAD/RPAD, LTRIM/RTRIM, REPLACE, TRANSLATE, CONCAT.
  • Numeric functions: ROUND, TRUNC, MOD, ABS, POWER.
  • Date functions: MONTHS_BETWEEN, ADD_MONTHS, NEXT_DAY, LAST_DAY, TO_DATE, ROUND, TRUNC, CURRENT_DATE.
  • Regular expressions: REGEXP_LIKE, REGEXP_SUBSTR.
  • Other functions: NVL, NVL2, NULLIF, TO_CHAR, DECODE, CASE expression, SOUNDEX, TO_NUMBER.

Case Conversion Functions

  • Alter the case of data.
  • Affect display in SELECT clause.
  • Modify case for search conditions in WHERE clause.

LOWER Function

  • Converts characters to lowercase.

UPPER Function

  • Converts characters to uppercase.
  • Syntax: UPPER(c) where c is the string or field.

INITCAP Function

  • Converts characters to mixed case.

Character Manipulation Functions

  • Manipulate data by extracting substrings, counting characters, replacing strings, etc.

SUBSTR Function

  • Returns a substring.

INSTR Function

  • Returns the position of a substring within a string.

LENGTH Function

  • Determines the number of characters in a string.

LPAD and RPAD Functions

  • Pad a string to a fixed width.

LTRIM and RTRIM Functions

  • Remove a specific string of characters.

REPLACE Function

  • Substitutes a string with another string.

TRANSLATE Function

  • Replaces characters with specified characters.

CONCAT Function

  • Concatenates two character strings.

Number Functions

  • Manipulate numeric data.
  • Includes ROUND, TRUNC, MOD, ABS.

ROUND Function

  • Rounds numeric columns to a specified precision.

TRUNC Function

  • Truncates a numeric value to a specific position.

MOD Function

  • Returns the remainder of a division operation.

ABS Function

  • Returns the absolute value of a number.

Date Functions

  • Perform date calculations or format date values.
  • Subtract dates to find the number of days difference.

MONTHS_BETWEEN Function

  • Determines the number of months between two dates.

ADD_MONTHS Function

  • Adds a specified number of months to a date.

NEXT_DAY Function

  • Determines the next occurrence of a specified day of the week.

TO_DATE Function

  • Converts various date formats to the internal Oracle format (DD-MON-YY).

ROUND Function (Dates)

  • Rounds dates to the nearest month or year.

TRUNC Function (Dates)

  • Truncates dates to a specified level of precision.

Format Model Elements - Dates

  • MONTH, MON, MM, RM, D, DD, DDD, DAY, DY, YYYY, YYY, YY, Y, YEAR, B.C. or A.D.

Regular Expressions

  • Describe complex patterns in textual data.

REGEXP_LIKE

  • Checks whether a string matches a regular expression.

Other Functions

  • NVL, NVL2, TO_CHAR, DECODE, SOUNDEX.

NVL Function

  • Substitutes a value for a NULL value.

NVL2 Function

  • Allows different actions based on whether a value is NULL.

NULLIF Function

  • Returns NULL if two expressions are equal.

TO_CHAR Function

  • Converts dates and numbers to a formatted character string.

Format Model Elements - Time and Number

  • Time: SS, SSSS, MI, HH/HH12, HH24, A.M./P.M.
  • Number: 9, 0, $, ., ,

DECODE Function

  • Determines action based upon values in a list.

CASE Expression

  • Evaluates conditions to determine the resulting value.

SOUNDEX Function

  • References phonetic representation of words.

TO_NUMBER Function

  • Converts a character string to a number.

DUAL Table

  • Dummy table with one column and one row.
  • Used for table reference in the FROM clause.

Summary

  • Single-row functions return a result for each row processed.
  • Case conversion functions alter the case of strings.
  • Character manipulation functions extract substrings, find positions, replace strings, determine length, and trim characters.
  • Nesting functions allows multiple operations.
  • Number functions like ROUND and TRUNC manipulate numeric data.
  • MOD returns the remainder of a division.
  • Date functions perform calculations and format dates.
  • Regular expressions enable complex pattern matching.
  • NVL, NVL2, and NULLIF handle NULL values.
  • TO_CHAR formats numeric data and dates.
  • DECODE determines actions based on values.
  • CASE expressions evaluate conditions.
  • SOUNDEX finds records based on phonetic representation.
  • The DUAL table is helpful for testing functions.