Skip to content

conversionFunction.md

Latest commit

 

History

History
96 lines (80 loc) · 5.25 KB

conversionFunction.md

File metadata and controls

96 lines (80 loc) · 5.25 KB
  • có 2 loại:
    • implicit: oracle tự động convert (varchar/char <=> number/date tự động)
    • explicit: user chủ động convert

TO_CHAR

  • The syntax for the TO_CHAR function in Oracle/PLSQL is: TO_CHAR( value [, format_mask] [, nls_language] )
  • trong đó:
    • 9: đại diện cho 1 số
    • 0: bắt buộc thêm 0 vào hiển thị
    • .: decimal number
    • $: thêm kí tự $ vào
    • ,: phân tích phần nghìn
    • $: Currency symbol.
    • 9: A digit placeholder (suppresses leading zeros).
    • 0: A digit placeholder (shows leading zeros).
    • G: Group separator (e.g., comma).
    • D: Decimal separator (e.g., period).
    • V: Multiplies the number by 10^n (used for decimal places, not suitable here).
  • Key Rules for TO_CHAR (number, format_mask):
    • Rounding: Numbers are rounded to the specified number of decimal places in the format mask. In '$9,999', there are no decimal places specified, so the number will be rounded to the nearest integer.
    • Overflow: If the number of significant digits to the left of the decimal point exceeds the number of 9s in the format mask (excluding the comma), the output will be replaced with hash signs (#######).
  • ví dụ với number:
TO_CHAR(1210.73, '9999.9'): Result: ' 1210.7'
TO_CHAR(-1210.73, '9999.9'): Result: '-1210.7'
TO_CHAR(1210.73, '9,999.99'): Result: ' 1,210.73'
TO_CHAR(1210.73, '$9,999.00'): Result: ' $1,210.73'
TO_CHAR(21, '000099'): Result: ' 000021'
  • với date: các format tương tự
    • You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.
      • TO_CHAR(sysdate, 'FMMonth DD, YYYY'): Result: 'July 9, 2003'
      • The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

TO_DATE

  • The syntax for the TO_DATE function in Oracle/PLSQL is: TO_DATE( string1 [, format_mask] [, nls_language] )

TO_NUMBER:

  • The Oracle/PLSQL TO_NUMBER function converts a string to a number.

NVL

  • nếu ko null => trả về string1, nếu null => trả về replace_with
  • The syntax for the NVL function in Oracle/PLSQL is: NVL( string1, replace_with )
  • Parameters or Arguments
    • string1: The string to test for a null value.
    • replace_with: The value returned if string1 is null.
  • The arguments expr1 and expr2 can have any data type. If their data types are different, then Oracle Database implicitly converts one to the other. If they cannot be converted implicitly, then the database returns an error. The implicit conversion is implemented as follows:

NVL2:

  • The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.
  • cú pháp: NVL2( string1, value_if_not_null, value_if_null )
  • Parameters or Arguments
    • string1: The string to test for a null value.
    • value_if_not_null: The value returned if string1 is not null.
    • value_if_null: The value returned if string1 is null.
    • 2 giá trị trả về phải cùng kiểu

NULLIF

  • The syntax for the NULLIF function in Oracle/PLSQL is: NULLIF( expr1, expr2 )
  • Parameters or Arguments
    • expr1: First value to compare. Must be either a numeric value or a value that is the same datatype as expr2.
    • expr2: Second value to compare. Must be either a numeric value or a value that is the same datatype as expr1.
  • return
    • The NULLIF function returns NULL if expr1 and expr2 are equal.
    • The NULLIF function returns expr1 if expr1 and expr2 are not equal.
  • expr1 can be an expression that evaluates to NULL, but it can not be the literal NULL.
    • NULLIF(NULL, 12): Result: ORA-00932 error (because expr1 can not be the literal NULL)

COALESCE:

  • The Oracle/PLSQL COALESCE function returns the first non-null expression in the list. If all expressions evaluate to null, then the COALESCE function will return null.
  • COALESCE( expr1, expr2, ... expr_n )

DECODE:

  • The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.
  • https://www.techonthenet.com/oracle/functions/decode.php
  • Syntax: DECODE( expression , search , result [, search , result]... [, default] )
  • Parameters or Arguments
    • expression: The value to compare. It is automatically converted to the data type of the first search value before comparing.
    • search: The value that is compared against expression. All search values are automatically converted to the data type of the first search value before comparing.
    • result: The value returned, if expression is equal to search.
    • default: Optional. If no matches are found, the DECODE function will return default. If default is omitted, then the DECODE function will return NULL (if no matches are found).
  • Returns
    • The DECODE function returns a value that is the same datatype as the first result in the list.
    • If the first result is NULL, then the return value is converted to VARCHAR2.
    • If the first result has a datatype of CHAR, then the return value is converted to VARCHAR2.
    • If no matches are found, the default value is returned.
    • If default is omitted and no matches are found, then NULL is returned.