Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
  • Single Row Function in Oracle (Character Function)

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 0
    • 0
    • 2.18k
    Comment on it

    These functions operate on single rows only and return one result per row. There are different types of single-row functions.





    Single-row functions are used to manipulate data items. They accept one or more arguments and return one value for each row returned by the query. Can be used in SELECT, WHERE, and ORDER BY clauses; can be nested.


    function_name [(arg1, arg2,...)]

    function_name is the name of the function.

    arg1, arg2 is any argument to be used by the function. This can be represented by a column name or expression.

    Types of Single Row Function

    1) Character functions:- Accept character input and can return both character and number values.

    They are of two types: -

    a) Case-manipulation functions. EX:- LOWER, UPPER, INITCAP

    b) Character-manipulation functions. EX-:- CONCAT, SUBSTR, LENGTH, INSTR, LPAD | RPAD, TRIM, REPLACE

    Queries that shows the use of case-manipulation functions:-

    SELECT 'The job id for '||UPPER(last_name)||' is ' ||LOWER(job_id) AS "EMPLOYEE DETAILS" FROM employees;

    The above query used the two case-manipulation functions i.e. UPPER(last name) which shows all the last name in uppercase and LOWER(jobid) which shows all job id in lower case. The result of the above query is like this:-

    Column name = EMPLOYEE DETAILS

    and rows are like this The job id for JOSHI is android_developer.

    Suppose you want to display the employee number, first name and department name of the employee whose last name is "joshi" and you don't its case either it is in lower case or in upper case, then use this query to fetch the result

    SELECT employee_id, last_name, department_id
    FROM employees
    WHERE LOWER(last_name) = joshi;

    2) Character-Manipulation Functions

    a) CONCAT: Joins values together (You are limited to using two parameters with CONCAT.)
    CONCAT(Hello, World) => HelloWorld
    b) SUBSTR: Extracts a string of determined length
    SUBSTR(HelloWorld,1,5) => Hello
    c) LENGTH: Shows the length of a string as a numeric value
    LENGTH(HelloWorld) => 10
    d) INSTR: Finds numeric position of a named character
    INSTR(HelloWorld, W) => 6
    e) LPAD: Pads the character value right-justified
    LPAD(salary,10,a) => aaaaa24000
    f) RPAD: Pads the character value left-justified
    RPAD(salary, 10, a) => 24000aaaaa
    g) TRIM: Trims heading or trailing characters (or both) from a character string (If trimcharacter or trimsource is a character literal, you must enclose it in single quotes.)
    TRIM(H FROM HelloWorld) => elloWorld

    SELECT employee_id, CONCAT(first_name, last_name) NAME,
    job_id, LENGTH (last_name),
    INSTR(last_name, a) "Contains a?"
    FROM employees
    WHERE SUBSTR(job_id, 4) = REP;

    The above query displays employee first names and last names joined together, the length of the employee last name, and the numeric position of the letter a in the employee last name for all employees who have the string REP contained in the job ID starting at the fourth position of the job ID.

    Hope you like this blog :). Rest of the single value functions will be covered in next blog.
    References: Oracle docs

 0 Comment(s)

Sign In

Sign up using

Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: