1 / 13

SQL 101 2 nd Session

SQL 101 2 nd Session. Functions: - Character functions  today - Number functions - Single Row functions. Character Functions: LOWER UPPER INITCAP LPAD RPAD LTRIM RTRIM TRIM SUBSTR INSTR LENGTH in WHERE & GROUP BY clauses Nested functions Concatenation

jean
Télécharger la présentation

SQL 101 2 nd Session

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQL 101 2nd Session

  2. Functions: - Character functions  today - Number functions - Single Row functions

  3. Character Functions: LOWER UPPER INITCAP LPAD RPAD LTRIM RTRIM TRIM SUBSTR INSTR LENGTH in WHERE & GROUP BY clauses Nested functions Concatenation REPLACE TRANSLATE SOUNDEX

  4. Single-Row Functions: NVL COALESCE NVL2 LNNVL NULLIF NANVL DECODE Searched CASE Simple CASE

  5. Number Functions: ABS SIGN ROUND TRUNC FLOOR CEIL MOD REMAINDER Floating point numbers Arithmetic operators

  6. Character functions.... UPPER(char) LOWER(char) INITCAP(char) LPAD(char1, n [, char2]) RPAD(char1, n [, char2]) SELECT state, LOWER(state), LOWER('State') FROM zipcode; SELECT UPPER(city) as "Upper Case City", state, INITCAP(state) FROM zipcode; WHERE zip = '10035'; SELECT RPAD(city, 20, '*') as "City Name", LPAD(state, 10, '-') as "State Name" FROM zipcode;

  7. Character functions.... The DUAL table Character functions.... The DUAL table contains a single row and a single column called DUMMY. It’s often used with functions to select values that do not exist in tables such as today’s date. A single row is always returned in the result set. This table is owned by SYS and is available to all users. SELECT * from dual;

  8. Character functions.... LTRIM(char1 [, char2]) RTRIM(char1 [, char2]) TRIM([LEADING|TRAILING|BOTH] char1 FROM char2) TRIM(char2) SUBSTR(char1, start_pos [, substr_length]) SELECT LTRIM('0001234500', '0') left, RTRIM('0001234500', '0') right, LTRIM(RTRIM('0001234500', '0'), '0') both FROM dual; SELECT TRIM(LEADING'0' FROM '0001234500') leading, TRIM(TRAILING '0' FROM '0001234500') trailing, TRIM('0' FROM '0001234500') both FROM dual; Column "1-5" format a6 Column "6 only" format a6 SELECT last_name, SUBSTR(last_name, 1, 5) as "1-5", SUBSTR(last_name, 6) as "6 only" FROM student;

  9. Character functions.... INSTR(char1, char2 [,start_pos [, occurrence]]) LENGTH(char1) Column description format a30 Column "er" format 999 SELECT description, INSTR(description, 'er') "er" FROM course; SELECT LENGTH('Hello there') FROM dual; SELECT first_name, last_name FROM student WHERE SUBSTR(last_name, 1, 2) = 'Mo';

  10. WHERE and GROUP BY clauses: SELECT first_name, last_name FROM student WHERE INSTR(first_name, '.') > 0 ORDER BY LENGTH(last_name); Nested Functions: Column mi format a3 Column first format a10 SELECT first_name, SUBSTR(first_name, INSTR(first_name, '.') -1) mi, SUBSTR(first_name, 1, INSTR(first_name, '.') -2) first FROM student WHERE INSTR(first_name, '.') >= 3;

  11. Concatenation: SELECT CONCAT(city, state) FROM zipcode; SELECT city||state||zip FROM zipcode; SELECT city||', '||state||' '||zip FROM zipcode;

  12. Character functions.... REPLACE(char, if, then) TRANSLATE(char, if, then) SELECT REPLACE('My hand is asleep', 'hand', 'foot') FROM dual; SELECT REPLACE('My hand is asleep', 'X', 'foot') FROM dual; SELECT phone FROM student WHERE TRANSLATE(phone, '0123456789','##########') <> '###-###-####'; SELECT phone FROM student WHERE TRANSLATE(phone, '012345678','##########') <> '###-###-####';

  13. Soundex: SELECT student_id, last_name FROM student WHERE SOUNDEX(last_name) = SOUNDEX('Torch'); Just my 2cents.... This function is crap.... Don't ever use BUT, know it exists.

More Related