Character, Number, Miscellaneous and Date Functions

1. Character, Number, Miscellaneous and Date Functions CS 265 EA Summer I 2007 Karl R. Wurst

2. Objectives • To understand how to use functions in your SQL statements • To be able to construct some Oracle functions that do not exist in SQLite from other SQLite functions • To understand SQLite’s date format • To use SQLite date functions to manipulate dates

3. Functions • Functions let you change the data returned from the database. • Functions follow the basic form:function_name(input_parameter, …) • Each function has a name • Functions generally take one or more input parameters • Each function returns a value. • All of the functions we will talk about today operate on a single row

4. The LOWER Function • The LOWER function changes all letters in a string to lowercase SELECT state, LOWER(state), LOWER(‘State’) FROM zipcode; • What will be the output?

5. The UPPER Function • The UPPER function changes all letters in a string to uppercase SELECT state, UPPER(state), UPPER(‘State’) FROM zipcode;

6. The INITCAP Function • The INITCAP function changes the first letter to uppercase and the rest of the letters to lowercase • But, SQLite does not provide this function! • However, we can build it if we have some other functions. • We already have UPPER and LOWER, what else do we need to be able to do?

7. The SUBSTR Function: Taking Strings Apart • The SUBSTR function lets you get at part of a string • You need to know the starting position and how many characters you wantSUBSTR(string, starting_position, length) starting_position can be negative (from the end of the string) • To get the first character: SUBSTR(‘Example String’, 1, 1) • How do we get the rest of the string?SUBSTR(‘Example String’, 2, ?) • What do we specify as the length?

8. The LENGTH Function:Finding a String’s Length • The LENGTH function tells us how long a string is LENGTH(‘Example String’)

9. So, how do we get the rest of the string? • The LENGTH function tells us how long a string is SUBSTR( ‘Example String’, 2, LENGTH(‘Example String’)-1) • (It turns out you don’t need the -1. Why?)

10. Now we can get the two parts of the string, and we can upper- and lowercase them. UPPER(SUBSTR(‘Example String’, 1, 1)) LOWER(SUBSTR(‘Example String, 2, LENGTH(‘Example String’)-1)) • Now what do we need to do?

11. Concatenation: Putting Strings Together • Concatenation lets you take two strings and combine them into one • SQLite uses the double vertical bar (||) for concatenation • (The Oracle CONCAT function is not available.) • Put the || between the two strings you want concatenatedSELECT city||state||zip FROM zipcode;

12. Now we can get the two parts of the string, and we can upper- and lowercase them, and we can put them together. UPPER(SUBSTR(‘Example String’, 1, 1)) || LOWER(SUBSTR(‘Example String, 2, LENGTH(‘Example String’)-1)) • This will let us do what INITCAP does

13. The LPAD and RPAD Functions • The LPAD and RPAD functions let you pad the left or right of a string to a set number of positions with a padding character • LPAD(‘Example’, 20, ‘*”) produces Example************* • RPAD(‘Example’, 20, ‘*”) produces *************Example • But, SQLite doesn’t provide them either. • Can we make them?

14. The LPAD and RPAD Functions • What do we need to make them? • Concatenation (||) • SUBSTR • LENGTH

15. The LPAD and RPAD Functions • Can you make them? LPAD(string, n, pad_char) can be produced by SUBSTR(pad_char_string, 1,n-LENGTH(string))|| string Where pad_char_string is a string of length n of the pad_char • For example: SUBSTR(‘********************’, 1,20-LENGTH(‘Example’))|| ‘Example’ • Can you do RPAD?

16. The LTRIM, RTRIM and TRIM Functions • Trim a specified character off left, right or both left and right ends of a string • LTRIM(string, trim_char) • RTRIM(string, trim_char) • TRIM(string, trim_char) • For example: LTRIM(‘0001234500’, ‘0’)produces 1234500

17. The REPLACE Function • Replaces all occurences of one string with another • REPLACE(string, find, replace) • For example: REPLACE(‘Oracle is a database’, ‘Oracle’, ‘SQLite’)produces SQLite is a database

18. The Numerical Functions • You may use the standard arithmetic operations in a SQL statement • Addition (+) • Subtraction (-) • Multiplication (*) • Division (/)

19. The ABS Function • Returns the absolute value of a number • ABS(value)

20. The ROUND Function • Rounds a number to the specified place • ROUND(value, precision) • Positive values for precision specify places to the right of the decimal point • Negative values for precision specify places to the left of the decimal point

21. The MOD Function • Returns the remainder of a division • MOD(value, divisor) • But, SQLite doesn’t provide the MOD function • However, we can create it: (value – (divisor*ROUND(value/divisor)))

22. The COALESCE Function • Replaces NULL values with one of a number of expressions • COALESCE(input_expression, substitute_expression1, …) • If input_expression is NULL, it will be replaced with substitute_expression1 • If substitute_expression1 is also NULL, substitute_expression2 will be used • And so on…

23. The NVL, NVL2 and NANVL Functions • The Oracle NVL function can be replaced with COALESCE with only one substitute_expression • The Oracle NVL2 function does not exist in SQLite, and there is no substitute that I have determined • The Oracle NANVL function is not needed in SQLite because there are no BINARY_FLOAT and BINARY_DOUBLE numbers

24. The NULLIF Function • The NULLIF function compares two expressions, and if they are equal, returns NULL SELECT student_id, created_date, modified_date, NULLIF(created_date, modified_date)FROM student;

25. The DECODE Function and CASE Expression • The Oracle DECODE function does not exist in SQLite, and there is no substitute that I have determined • The Oracle CASE function does not exist in SQLite, and there is no substitute that I have determined • This is a problem • I am still looking for a solution

26. SQLite Dates • There is no SQL standard for dates • Dates in SQLite are text fields • A time string can be in any of the following formats: • YYYY-MM-DD • YYYY-MM-DD HH:MM • YYYY-MM-DD HH:MM:SS • YYYY-MM-DD HH:MM:SS.SSS • YYYY-MM-DDTHH:MM • YYYY-MM-DDTHH:MM:SS • YYYY-MM-DDTHH:MM:SS.SSS • HH:MM • HH:MM:SS • HH:MM:SS.SSS • now • DDDD.DDDD Assume 2000-01-01 Julian date

27. SQLite Date Functions • date( timestring, modifier, modifier, ...) returns YYYY-MM-DD • time( timestring, modifier, modifier, ...) returns HH:MM:SS • datetime( timestring, modifier, modifier, ...) returns YYYY-MM-DD HH:MM:SS • julianday( timestring, modifier, modifier, ...)returns the number of days since noon in Greenwich on November 24, 4714 B.C

28. SQLite Date Functions • strftime( format, timestring, modifier, modifier, ...) provides custom output using the format string. • The format string can include: %d day of month %f fractional seconds SS.SSS %H hour 00-24 %j day of year 001-366 %J Julian day number %m month 01-12 %M minute 00-59 %s seconds since 1970-01-01 %S seconds 00-59 %w day of week 0-6 sunday==0 %W week of year 00-53 %Y year 0000-9999 %% %

29. SQLite Date Functions • Notice that all of the other four functions could be expressed in terms of strftime(). • date(...) -> strftime("%Y-%m-%d", ...) • time(...) -> strftime("%H:%M:%S", ...) • datetime(...) -> strftime("%Y-%m-%d %H:%M:%S", ...) • julianday(...) -> strftime("%J", ...) • The only reasons for providing functions other than strftime() is for convenience and for efficiency.

30. SQLite Date Modifiers • The time string can be followed by zero or more modifiers that alter the date or alter the interpretation of the date. The available modifiers are as follows. • NNN days • NNN hours • NNN minutes • NNN.NNNN seconds • NNN months • NNN years • These modifiers simply add the specified amount of time to the date specified by the preceding timestring.

31. SQLite Date Modifiers • The time string can be followed by zero or more modifiers that alter the date or alter the interpretation of the date. The available modifiers are as follows. • start of month • start of year • start of day • weekday N • The “start of” modifiers shift the date backwards to the beginning of the current month, year or day. • The "weekday" modifier advances the date forward to the next date where the weekday number is N. Sunday is 0, Monday is 1, and so forth.

32. SQLite Date Modifiers • The time string can be followed by zero or more modifiers that alter the date or alter the interpretation of the date. The available modifiers are as follows. • unixepoch • localtime • utc • The "unixepoch" modifier only works if it immediately follows a timestring in the DDDDDDDDDD format. This modifier causes the DDDDDDDDDD to be interpreted not as a julian day number as it normally would be, but as the number of seconds since 1970. This modifier allows unix-based times to be converted to julian day numbers easily. • The "localtime" modifier adjusts the previous time string so that it displays the correct local time. "utc" undoes this.

33. SQLite Date Examples • Compute the current date. SELECT date('now'); • Compute the last day of the current month. SELECT date('now','start of month', '+1 month','-1 day'); • Compute the number of days since the battle of Hastings. SELECT julianday('now') - julianday('1066-10-14','gregorian');

34. SQLite Date Examples • Compute the number of seconds between two dates: SELECT julianday('now')*86400 - julianday('2004-01-01 02:34:56') *86400; • Compute the date of the first Tuesday in October (January + 9) for the current year. SELECT date('now','start of year', '+9 months','weekday 2');

35. Searching for Dates • Why do I get no results for this query? (There are student records created on this date) SELECT student_id FROM student WHERE created_date = ‘2003-02-2003’;

36. Searching for Dates • I am interested in only the date, not the time • How can I do that? SELECT student_id FROM student WHERE date(created_date) = ‘2003-02-2003’;

37. References • SQL As Understood By SQLite (Expressions): http://www.sqlite.org/lang_expr.html • SQLite Date and Time Functions: http://www.sqlite.org/cvstrac/wiki/wiki?p=DateAndTimeFunctions