1 / 38

Chapter 4 Character, number, and Miscellaneous functions

seoras
Download Presentation

Chapter 4 Character, number, and Miscellaneous functions

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. Data WarehousingTarleton state universityMath 586Instructors: keithemmert, Sean perry, Micha Roberson, and Michael Schuckingcourse materials: oracle SQL By Example, 4th Edition by Alice RischerTSoftware: Oracle 11GTextbook resource website:http://www.oraclesqlbyexample.com/course Objectives:Understand and apply fundamental SQL functions, expressions, commands etc.A. Create/drop tables/databaseB. Query tablESC. Use aggregate functionsD. USE INNER/OUTER/LEFT/RIGHT JOINSE. Write queries with STRING, NUMERIC, CONVERSION, and date/time functions. CONDUCT QA and integrity checks.

  2. Chapter 4 Character, number, and Miscellaneous functions • A function is a type of formula whose result is one of two things: either a transformation or information. • Syntax Symbols

  3. Lab 4.1: Character functionsLOWER • The LOWER function transforms data into lowercase. SELECT state, LOWER(state), LOWER('STATE'), LOWER('State') FROM zipcode; STATE LOWER(STATE) LOWER('STATE') LOWER('STATE') ----- ------------ -------------- -------------- CT ct state state FL fl state state OH oh state state MI mi state state GA ga state state NY ny state state MA ma state state NJ nj state state WV wv state state PR pr state state 10 rows selected

  4. UPPER and INITCAP • The UPPER function transforms data into UPPERCASE. • The INITCAP functions capitalizes the first letter and lowercases the rest of the word. • SELECT DISTINCT state, UPPER(state), INITCAP(state) FROM zipcode; STATE UPPER(STATE) INITCAP(STATE) ----- ------------ -------------- NJ NJ Nj MA MA Ma CT CT Ct GA GA Ga WV WV Wv FL FL Fl PR PR Pr NY NY Ny OH OH Oh MI MI Mi 10 rows selected

  5. The LPAD and RPAD functions • Transforms data by padding strings to the left(LPAD) or to the right(RPAD). • There is a third, optional input parameter to specify which character the string should be padded with. Strings are padded with spaces by default. Remember that the second input is the maximum TOTAL character length. • SELECT RPAD('yeah', 9, '!'), LPAD('no', 4, '?') from dual; RPAD('YEAH',9,'!') LPAD('NO',4,'?') ------------------ ---------------- yeah!!!!! ??no

  6. The DUAL Table • DUAL is a table unique to Oracle. It contains a single row and a single column called DUMMY. • Used to call functions with string literals as input that does not exist in a table. • See example on previous slide.

  7. The LTRIM, RTRIm, and TRIm Functions • LTRIM and RTRIM remove all unwanted characters in a string. Blanks spaced are removed by default. • The second input to the function is the character to be removed. SELECT LTRIM('000012340', '0'), RTRIM('0123400000', '0'), LTRIM(RTRIM('000012340000','0'), '0') FROM dual; LTRIM('000012340','0') RTRIM('0123400000','0') LTRIM(RTRIM('000012340000','0'), '0') ---------------------- ----------------------- ---------------------------- 12340 01234 1234

  8. TRIM function • Instead of nesting the LTRIM and RTRIM functions, we can use TRIM to achieve the same results. Keywords are LEADING, TRAILING, and the default BOTH. SELECT TRIM(LEADING '0' FROM '000012340'), TRIM(TRAILING '0' FROM '0123400000'), TRIM('0' FROM '000012340000') FROM dual; TRIM(LEADING'0'FROM'000012340') TRIM(TRAILING'0'FROM'0123400000') TRIM('0'FROM'000012340000') ------------------------------- --------------------------------- --------------------------- 12340 01234 1234

  9. The SUBSTR Function • SUBSTR transforms a string, returning a substring or a subset of a string, based on its input parameters. • Takes two input parameters and an optional third. SUBSTR(input, starting_postition, ending_position) SELECT substr('I love MATH!', 1,1), substr('I love MATH!', 2,6), substr('I love MATH!', 7) FROM dual; SUBSTR('ILOVEMATH!',1,1) SUBSTR('ILOVEMATH!',2,6) SUBSTR('ILOVEMATH!',7) ------------------------ ------------------------ ---------------------- I love MATH!

  10. THE INSTR function • INSTR, meaning in string, looks for the occurrence of a string inside another string, returning the starting position of the search string within the target string. SELECT INSTR('linear', 'er'), INSTR('hyperbolic', 'er') FROM dual; INSTR('LINEAR','ER') INSTR('HYPERBOLIC','ER') ---------------------- ------------------------ 0 4

  11. The LENGTH Function • The LENGTH function determines the length of a string, expressed as a number. SELECT length('linear') FROM dual; LENGTH('LINEAR') ---------------------- 6

  12. Functions in WHERE and ORDER BY clauses • Functions can be used anywhere an expression can be used. SELECT first_name, last_name FROM sharma.student WHERE instr(first_name, '.')>0 ORDER BY LENGTH(last_name); FIRST_NAME LAST_NAME ------------------------- ------------------------- Suzanne M. Abid J. Dalvi Austin V. Cadet D. Orent

  13. Nested functions • As we saw in the earlier example using LTRIM(RTRIM(in)), functions can be nested. • The are evaluated starting from the inner function and working outward. SELECT first_name, instr(SUBSTR(first_name, 1,5), 'rr'), last_name FROM student; FIRST_NAME INNERSTRING LAST_NAME ------------------------- ---------------------- ------------------------- Pierre 4 Radicola Derrick 3 Baltazar Gerry 3 Tripp

  14. CONCATENATION • Concatenation connects strings together to become one. • Two different concatenation methods • The CONCAT function: Select CONCAT(‘me’, ‘ too!’) from DUAL; ‘me too!’ • The || symbol. Select ‘me’ || ‘ too!’ from DUAL; ‘me too!’

  15. The replace function • The REPLACE function replaces one string with another string. SELECT REPLACE('My hand is asleep', 'hand', 'foot') FROM DUAL; REPLACE('MYHANDISASLEEP','HAND','FOOT') --------------------------------------- My foot is asleep

  16. The TRANSLATE function • The TRANSLATE function provides a one-for-one character substitution. SELECT TRANSLATE('My hand is asleep', 'ae', 'io') FROM DUAL; TRANSLATE('MYHANDISASLEEP','AE','IO') ------------------------------------- My hind is isloop

  17. The soundex function • The SOUNDEX functions allows you to compare differently spelled words that phonetically sound alike. SELECT student_id, last_name FROM sharma.student WHERE soundex(last_name) = soundex('MARTIN'); STUDENT_ID LAST_NAME ---------------------- ------------------------- 110 Martin 324 Marten 393 Martin

  18. SQL Developer SNIPPETS • SQL functions or syntax examples that are frequently used are stored and called snippets. • They can be accessed by going to ViewSnippets in SQL Developer

  19. Lab 4.1: Exercises • Go to page 147 in book • Answer and discuss answers in class.

  20. Lab 4.2: Number functionsABS and SIGN • The ABS Function • The ABS function computes the absolute value of a number, measuring its magnitude. SELECT 'The absolute value of -25 is ' || ABS(-25) FROM DUAL; 'THEABSOLUTEVALUEOF-25IS'||ABS(-25) ----------------------------------- The absolute value of -25 is 25 • The SIGN Function • The SIGN function tells you the sign of a value, returning a number 1 for positive numbers, -1 for negative numbers, and 0 for zero. SELECT -14, SIGN(-14), SIGN(14), SIGN(0) FROM DUAL; -14 SIGN(-14) SIGN(14) SIGN(0) ---------------------- ---------------------- ---------------------- ---------------------- -14 -1 1 0

  21. Round and trunc Functions • ROUND rounds a value to a given number of digits of precision. TRUNC cuts off a value to a given number of digits of precisions. • They each have two inputs, the number to be rounded/truncated and the number of places to round/truncate to. A negative value in the second input will round/truncate to n digits to the left of the decimal point. SELECT 22.0051, ROUND(22.0051, 2), ROUND(22.0051, -2) FROM DUAL; 22.0051 ROUND(22.0051,2) ROUND(22.0051,-2) ---------------------- ---------------------- ---------------------- 22.0051 22.01 0 SELECT 22.0051, TRUNC(22.0051, 2), TRUNC(22.0051, -2) FROM DUAL; 22.0051 TRUNC(22.0051,2) TRUNC(22.0051,-2) ---------------------- ---------------------- ---------------------- 22.0051 22 0

  22. The FLOOR and CEIL Functions • The CEIL function returns the smallest integer greater than or equal to a value; the FLOOR function returns the largest integer equal to or less than a value. SELECT FLOOR(22.5), CEIL(22.5) FROM DUAL; FLOOR(22.5) CEIL(22.5) ---------------------- ---------------------- 22 23

  23. The MOD Function • MOD is a function that returns the modulus, or the remainder of a value divided by another value. • It takes two inputs, the number to be divided and the value to divide it by. MOD uses FLOOR in its computations. SELECT 25/6, MOD(25,6), 28/5, MOD(28,5) FROM DUAL; 25/6 MOD(25,6) 28/5 MOD(28,5) ---------------------- ---------------------- ---------------------- ---------------------- 4.16666667 1 5.6 3 25 – 6*(FLOOR(4.16)) =25-24 =1 28-5*(FLOOR(5.6)) = 28-25=3

  24. Floating-point numbers • Floating-point numbers support the IEEE standard for binary floating-point arithmetic. Computations can sometimes be in the order of 5-10x faster than NUMBER because floating-point data types use the native instruction set supplied by the hardware vendor. • BINARY_DOUBLE supports a wider range of values than does BINARY_FLOAT but they both consist of three components: a sign, the signed exponent, and significand. • The ROUND function takes on a different behavior if the input is BINARY_FLOAT or BINARY_DOUBLE, rounding to the nearest EVEN value. select ROUND(3.5), ROUND(3.5f), ROUND(4.5), ROUND(4.5f) from DUAL; ROUND(3.5) ROUND(3.5F) ROUND(4.5) ROUND(4.5F) ------------------ ----------- ---------------------- ----------- 4 4.0 5 4.0

  25. The REMAINDER Function • The REMAINDER function calculates the REMAINDER according to the IEEE specification. • It is similar to MOD, but uses ROUND instead of FLOOR in its calculations. SELECT 25/6, REMAINDER(25,6), 28/5, REMAINDER(28,5) FROM DUAL; 25/6 REMAINDER(25,6) 28/5 REMAINDER(28,5) ---------------------- ---------------------- ---------------------- ---------------------- 4.166666666667 1 5.6 -2 25 – 6*(ROUND(4.16)) =25-24 =1 28-5*(ROUND(5.6)) = 28-30=-2

  26. Arithmetic Operators • The four mathematical operators ( addition, subtraction, multiplication, and division) can used in a SQL statement and can be combined. • Parentheses are used to group computations, indicating precedence of the operators.

  27. Lab 4.2: Exercises • Go to page 163 in book • Answer and discuss answers in class.

  28. LAB 4.3: Miscellaneous Single-row functionsThe NVL Function • The NVL function replaces a NULL value with a default value. • Since a computation with an unknown value yields an unknown value, the NVL function can be used to replace the unknown value with a valid value. SELECT NVL(1,2) + NVL(NULL, 2) FROM DUAL; NVL(1,2)+NVL(NULL,2) ---------------------- 3

  29. The coalesce function • Similar to the NVL function except instead of specifying one substitution expression for a null value, you can optionally evaluate multiple substitution columns or substitution expressions. • If the first value is not null then the first value is returned else if the second value is not null then the second value is returned else if the third value is not null then the third value is returned, etc. SELECT student_id, midterm_grade, finalexam_grade, quiz_grade, COALESCE(midterm_grade, finalexam_grade, quiz_grade) as "Coalesce" from grade_summary; STUDENT_ID MIDTERM_GRADE FINALEXAM_GRADE QUIZ_GRADE Coalesce -------------- ---------------------- ---------------------- --------------- -------------- 123 90 50 100 90 456 80 95 80 678 98 98 789 78 85 78

  30. The NVL2 Function • This function checks for both not null and null values and has three parameters. • If the first input is not null then the second parameter is returned, else the third parameter is returned. SELECT NVL2(1,'Not Null', 'Null'), NVL2(NULL, 'Not Null', 'Null') FROM dual; NVL2(1,'NOTNULL','NULL') NVL2(NULL,'NOTNULL','NULL') ------------------------ --------------------------- Not Null Null

  31. The LNNVL Function • The LNNVL can ONLY be used in the WHERE clause. • It returns either true of false. It returns true and therefore a result if the condition is either false or unknown. SELECT course_no, cost FROM course WHERE LNNVL(cost<1500) COURSE_NO COST ---------------------- ---------------------- 80 1595 450

  32. The Nullif function • The NULLIF function is unique in that it generates null values. • The function compares two functions; if the values are equal, the function returns a null; otherwise, the function returns the first expression. SELECT NULLIF('1','1'), NULLIF('1','2') FROM dual; NULLIF('1','1') NULLIF('1','2') --------------- --------------- 1

  33. The NANVL Function • This function is used only for the BINARY_FLOAT and BINARY_DOUBLE floating-point data types. • The function returns a substitution value in case the input is NAN(“not a number”). SELECT NANVL(BINARY_FLOAT_NAN,1), NANVL(1,2) FROM dual; NANVL(BINARY_FLOAT_NAN,1) NANVL(1,2) ------------------------- ---------------------- 1.0 1

  34. The DECODE function • The DECODE function substitutes values based on a condition, using if-then-else logic. • If the input value is equal to the second parameter then the third value is returned else the fourth value is returned. • The search and result values can be repeated. SELECT DISTINCT state, DECODE(state, 'NY', 'New York', 'NJ', 'New Jersey', 'OTHER') FROM zipcode; STATE DECODE(STATE,'NY','NEWYORK','NJ','NEWJERSEY','OTHER') ----- ----------------------------------------------------- MA OTHER WV OTHER OH OTHER MI OTHER NJ New Jersey FL OTHER CT OTHER GA OTHER PR OTHER NY New York

  35. The decode function and nulls and comparisons • A NULL value can be explicitly passed to the DECODE function. SELECT 'NY',DECODE('NY', 'NY', 'New York', 'NJ', 'New Jersey', NULL, 'OTHER'), NULL, DECODE(NULL, 'NY', 'New York', 'NJ', 'New Jersey', NULL, 'OTHER') FROM dual; 'NY' DECODE('NY',…) NULL DECODE(NULL,…) --- --------------------- ------------ ------------------ NY New York OTHER • The DECODE function does not allow greater than or less than comparison; however, combining it with the SIGN function overcomes this shortcoming. SELECT course_no, cost, DECODE(SIGN(cost-1195), -1, 500, cost) newcost FROM course WHERE course_no IN (80, 20, 135, 450) ORDER BY 2; COURSE_NO COST NEWCOST ----------------- ----------- --------------- 135 1095 500 20 1195 1195 80 1595 1595

  36. The Searched case expression • Each CASE expression starts with keyword CASE and end with the keyword END; the ELSE clause is optional. • CASE expressions can be nested within each other and used in the where clause. You must be careful that each THEN keyword returns a value of the same data type. SELECT course_no, cost, CASE WHEN cost<1100 THEN 1000 WHEN cost>=1100 AND cost <1500 THEN cost*1.1 WHEN cost IS NULL THEN 0 ELSE cost END "test case" FROM course WHERE course_no IN (80, 20, 135, 450) ORDER BY 2; COURSE_NO COST test case ---------------------- ---------------------- ---------------------- 135 1095 1000 20 1195 1314.5 80 1595 1595 450 0

  37. The Simple case expression • If your conditions are testing for equality only, you can use a simple CASE expression. • The simple CASE expression has an additional input which the WHEN statements are tested against. SELECT course_no, cost, CASE cost WHEN 1095 THEN cost/2 WHEN 1195 THEN cost*1.1 WHEN 1595 THEN cost ELSE cost*.5 END "simple case" FROM course WHERE course_no IN (80, 20, 135, 450) ORDER BY 2; COURSE_NO COST simple case ---------------------- ---------------------- ---------------------- 135 1095 547.5 20 1195 1314.5 80 1595 1595 450

  38. Chapter 4 complete! • Quiz will be given at the beginning of our next class

More Related