Tutorialspoint.dev

SQL | Conversion Function


In some cases, the Server uses data of one type where it expects data of a different data type. This can happen when the Server can automatically convert the data to the expected data type. This data type conversion can be done implicitly by the Server, or explicitly by the user.

Implicit Data-Type Conversion :

In this type of conversion the data is converted from one type to another implicitly (by itself/automatically).

From To
VARCHAR2 or CHAR NUMBER
VARCHAR2 or CHAR DATE
DATE VARCHAR2
NUMBER VARCHAR2

    EXAMPLE :

  1. QUERY:
    SELECT employee_id,first_name,salary
    FROM employees
    WHERE salary > 15000;

    OUTPUT :

    Employee_ID FIRST_NAME SALARY
    100 Steven 24000
    101 Neena 17000
    102 lex 17000
  2. QUERY:
    SELECT employee_id,first_name,salary
    FROM employees
    WHERE salary > '15000';

    OUTPUT :

    Employee_ID FIRST_NAME SALARY
    100 Steven 24000
    101 Neena 17000
    102 lex 17000

    Here we see the output of both queries came out to be same,inspite of 2nd query using ‘15000’ as text, it is automatically converted into int data type.

  3. /ol>

    Explicit Data-Type Conversion :



    TO_CHAR Function :

    TO_CHAR function is used to typecast a numeric or date input to character type with a format model (optional).
    SYNTAX :

    TO_CHAR(number1, [format], [nls_parameter])

    Using the TO_CHAR Function with Dates :

    SYNTAX :

    TO_CHAR(date, ’format_model’)

    The format model:

    • Must be enclosed in single quotation marks and is case
      sensitive
    • Can include any valid date format element
    • Has an fm element to remove padded blanks or
      suppress leading zeros
    • Is separated from the date value by a comma

    EXAMPLE :

    SELECT employee_id, TO_CHAR(hire_date, ’MM/YY’) Month_Hired
    FROM employees
    WHERE last_name = ’Higgins’;

    OUTPUT :

    EMPLOYEE_ID MONTH_HIRED
    205 06/94

    Elements of the Date Format Model :

    YYYY Full year in Numbers
    YEAR Year spelled out
    MM Two digit value for month
    MONTH Full name of the month
    MON Three Letter abbreviation of the month
    DY Three letter abbreviation of the day of the week
    DAY Full Name of the of the week
    DD Numeric day of the month

    Elements of the Date Format Model :

    Date Format Elements – Time Formats :
    Use the formats listed in the following tables to display time information and literals and to change numerals to spelled numbers.

    ELEMENT DESCRIPTION
    AM or PM Meridian indicater
    A.M. or P.M. Meridian indicater with periods
    HH or HH12 or HH24 Hour of day,or hour (1-12),or hour (0-23)
    MI Minute 0-59
    SS Second 0-59
    SSSSS Second past Mid Night 0-86399

    Other Formats :

    ELEMENT DESCRIPTION
    / . , Punctuation is reproduced in the result
    “of the” Quoted string is reproduced in the result

    Specifying Suffixes to Influence Number Display :

    ELEMENT DESCRIPTION
    TH Ordinal Number (for example DDTH for 4TH
    SP Spelled out number (for example DDSP for FOUR
    SPTH or THSP spelled out ordinal numbers (for example DDSPTH for FOURTH

    EXAMPLE :



    SELECT last_name,
    TO_CHAR(hire_date, ’fmDD Month YYYY’)
    AS HIREDATE
    FROM employees;

    OUTPUT :

    LASTNAME HIIREDATE
    Austin 25 January 2005
    Shubham 20 June 2004
    Nishant 15 January 1999
    Ankit 15 July 1995
    Vanshika 5 August 2004
    Kusum 10 June 1994
    Faviet 11 March 2005
    King 9 April 1996

    Using the TO_CHAR Function with Numbers :

    SYNTAX :

    TO_CHAR(number, ’format_model’)

    These are some of the format elements you can use
    with the TO_CHAR function to display a number value
    as a character :

    9 Represent a number
    0 Forces a zero to be displayed
    $ places a floating dollar sign
    L Uses the floating local currency symbol
    . Print a decimal point
    , Prints a Thousand indicator

    EXAMPLE :

    SELECT TO_CHAR(salary, ’$99,999.00’) SALARY
    FROM employees
    WHERE last_name = ’Ernst’;

    OUTPUT :

    SALARY
    $5000

    Using the TO_NUMBER and TO_DATE Functions :

    Convert a character string to a number format using the TO_NUMBER function :

    TO_NUMBER(char[, ’format_model’])

    Convert a character string to a date format using the TO_DATE function:

    TO_DATE(char[, ’format_model’])

    These functions have an fx modifier. This modifier specifies the exact matching for the character argument and date format model of a TO_DATE function.
    EXAMPLE :

    SELECT last_name, hire_date
    FROM employees
    WHERE hire_date = TO_DATE(’May 24, 1999’, ’fxMonth DD, YYYY’);

    OUTPUT :

    LASTNAME HIREDATE
    Kumar 24-MAY-99



This article is attributed to GeeksforGeeks.org

You Might Also Like

leave a comment

code

0 Comments

load comments

Subscribe to Our Newsletter