SQL[ Section 1- unit 1 and 2] Case and Character Manipulation Functions
DUAL: a dummy table used to view results from functions and calculations
SELECT(400+600)/ 5
FROM DUAL; Following are the single-row functions (functions that operate on single rows only and return one result per row) that perform case conversion and/or character manipulation Character case-manipulation functions LOWER, UPPER, and INITCAP in a SQL query. Select and apply character-manipulation functions CONCAT, SUBSTR, LENGTH, INSTR, LPAD, RPAD, TRIM, and REPLACE in a SQL query
1. UPPER: Converts alpha characters to upper case 2. Lower: Converts alpha characters to upper case 3. INITCAP: Converts alpha character values to uppercase for the first letter of each word, all other letters in lowercase. Examples: SELECT UPPER(title) AS "Rock Stars FROM My_cds WHERE cd_number = 60; [converts title into upper case for cds with cd_ number = 60 in My_cds table] SELECT title FROM d_cds WHERE INITCAP(title) = 'Carpe Diem'; Case Conversion Functions: CONCAT: Concatenates [joins] the first character value to the second character value; equivalent to concatenation operator (||). SUBSTR: Returns specific characters from character value starting at a specific character position and going specified character positions long INSTR: Returns the numeric position of a named string. LENGTH: Returns the number of characters in the expression LPAD: Pads the left side of a character, resulting in a right-justified value RPAD: Pads the right-hand side of a character, resulting in a left- justified value. TRIM: Removes all specified characters from either the beginning or the ending of a string. REPLACE: Replaces a sequence of characters in a string with another set of characters.
Function CONCAT (Hello, World) SUBSTR (HelloWorld, 1, 5) Function LENGTH (HelloWorld) INSTR (HelloWorld, W) LPAD (salary, 10,*) RPAD (salary, 10, *) TRIM (H, FROM HelloWorld) Function REPLACE('JACK and JUE','J','BL')
Result HelloWorld Hello Result 10 6 *****24000 24000***** elloWorld Result BLACK and BLUE
SELECT LOWER (last_name)||LOWER(SUBSTR(first_name,1,1)) AS "User Name" FROM f_staffs; || can be used in place of CONCAT function SELECT * FROM employees WHERE last_name = :l_name [:substitution variable used for taking input from the user] We use * when we want to select all records. Number functions: These functions accept numeric input and return numeric values. 1. TRUNC: Used to terminate the column, expression, or value to a specified number of decimal places 2. MOD: Returns the remainder of a division. E.g. MOD (100, 2) 3. ROUND: Rounds the column, expression, or value to a set number of decimal places. Syntax is ROUND (column|expression, decimal places) TRUNC (column/expression, decimal places) Round (458.789, 2) FROM DUAL;
[ will give the output 458.79]
To round digits to the left of decimal place we use minus sign with the number. e.g. ROUND(458.79, -2)
Using the above SQL commands write solution for the following questions. Before attempting them read Section I of the Database Design and Programming with SQL. Email your homework to rsawhney@vasantvalley.org . The first one has been done for you.
Accessing the Apex Account Address: https://iacademy4.oracle.com School: IN_2910 User: IN_2910_SQL01_S01 [the number runs up to S30] Password: oracle 1. Using the three separate words "Oracle," "Internet," and "Academy," use one command to produce the following output: The Best Class Oracle Internet Academy Solution SELECT CONCAT('Oracle ', 'Internet')|| ' Academy' AS "The Best Class" FROM DUAL; AS The Best Class is used as a column alias. Alias is used when you want to give your own column heading in the output. 2. What's the position of I in "Oracle Internet Academy"? 3. Starting with the string "Oracle Internet Academy", pad the string to produce:
Oracle$$$Internet$$$Academy
4. Using the string Oracle Internet Academy, produce the output
REPLACE function. The Best Class Oracle 2009-2010 Academy
shown using the
5. Divide each employee's salary by 3. Display only those employees last names and
salaries who earn a salary that is a multiple of 3.
6. Use a MOD number function to determine whether 38873 is an even number or an odd
number.
7. Use the DUAL table to process the following numbers: a. 845.553 - round to one decimal place b. 30695.348 - round to two decimal places c. 30695.348 - round to -2 decimal places
d. 2.3454 - truncate the 454 from the decimal place 8. List the order date and the order total from the Global Fast Foods F_ORDERS table.
Name the order total as TOTAL, and fill in the empty spaces to the left of the order total with $.
9. Display employee last_name and salary for those employees who work in department
80.
