SQL | A miscellaneous collection of useful Oracle SQL functions.
A. Trunc
The ‘Trunc’ function allows you to truncate a date back to a required level of detail. It’s similar to how ‘truncate’ works for numeric fields (a value of 1.6 for instance would be truncated to 1). The following SQL selects the first day of the current year:
select trunc(SYSDATE,'YEAR') from dual;
The next SQL retrieves the first day of the current month:
select trunc(SYSDATE,'MONTH') from dual;
‘Trunc’ also allows you to truncate at the time level. The following will give you the date-time value at the start of the current hour:
select trunc(SYSDATE,'HH') from dual;
Refer to the link at the bottom of this post for a full list of the format patterns allowed by the ‘Trunc’ function.
B. Sample
Most developers typically look at a table by doing a very quick ‘select * from ps_table_name’. This will give you some idea of a table’s contents, but it might not be a representative sample set of the data. You might, for instance, get some older records at the top of the results set, possibly inserted in the early days of your project.
To get around this problem, include the ‘sample’ function at the end of your SQL select statement. This accepts one parameter: the percentage of records to include in the sample. So in the example below, we are selecting a random 1% sample of all records in ‘ps_person’:
select * from ps_person sample(1);
C. Cast
The ‘Cast’ function is used to convert data of one type to another type. This comes in useful for character fields that actually contain numeric data. The ‘Cast’ function allows you to treat the field as if it were a number, allowing you to perform numeric operations.
A good example of a character field that often contains numeric data comes from Campus Solutions. The primary grade field (CRSE_GRADE_INPUT) is defined as a character field, but most institutions typically store grades as a percentage value, from 0 to 100. What if you wanted to run a query to select all students who achieved less than 50% in a particular class? Something like this might do the job:
select emplid from ps_stdnt_enrl where cast(crse_grade_input as number) < 50;
Unfortunately, in this particular example, the statement will most likely generate an error. This is because there’s a high probability of the Grade field containing non-numeric values, in addition to the numbers. Even an empty field (filled with a single space only) would cause an error as the database cannot convert this field into a number. A quick way to get around this problem is to exclude the non-numeric values as part of your SQL statement:
select emplid from ps_stdnt_enrl where crse_grade_input <> ' ' and crse_grade_input <> 'X' and cast(crse_grade_input as number) < 50;
If you follow the above approach, make sure to specify the exclusions first, and then include the ‘cast’ function as the final statement in the SQL.
Another option might be use a regular expression ‘like’ to exclude fields containing character data:
select emplid from ps_stdnt_enrl where crse_grade_input <> ' ' and not regexp_like(crse_grade_input, '[:alpha:]') and cast(crse_grade_input as number) < 50;
You may need to analyse your data to work out the best way to exclude the non-numeric values (if you do a Google search, you will find some fairly complex solutions to this problem). For now, to keep things simple, we will stick to basic exclusions only.
D. SQL Windows Functions
The ‘Windows’ functions have nothing to do with the Microsoft operating system. Instead they are a whole suite of functions designed to give SQL a much broader range of functionality in terms of grouping and reporting. Crudely, it’s an extended version of the standard ‘GROUP BY’ functionality, allowing you to add specific processing and sorting on a group-by-group basis (hence, the name ‘window’… this gives the impression of having multiple points-of-view over the same set of data).
The ‘Windows’ functions can get very complicated very quickly, so we will stick to a basic example that introduces the functionality as a whole. The ‘over’ function allows you to specify the exact manner in which you wish to separate (partition) the data.
This is probably best explained with an example. Let’s say you have a table keyed on Customer ID (among other fields), and you want to give each row in the table a unique sequence number, starting at 1 for each customer. In addition, the sequence should be ordered by date, with the earliest date being assigned sequence number 1. So the end result should look something like this:
This can be handled with the use of the ‘over’ function:
select customer_id, order_date row_number() over (partition by customer_id order by order_date) seq_no from customer_orders;
As I said earlier, we could explore the SQL Windows functions in considerable depth, so this post should only be seen as the most basic of introductions.
E. Number to Words Function
Finally, we end with a very quirky use of SQL to get around a requirement that comes up from time-to-time: having to spell out a numeric value in words. A value of 105 for instance needs to come out as ‘one hundred and five’. A program to print cheques is the most obvious example of where this sort of code might be needed.
I’ve seen some elaborate solutions to this problem in both PL/SQL and PeopleCode, however there is a simple solution – albeit an imperfect one – available in standard Oracle SQL. This involves some ‘out of the box’ thinking though, as we are about to take advantage of a feature that has nothing to do with numbers: Julian dates.
A Julian date reduces a specific date in the calendar to an integer value, starting at a value of 1 for January 1, 4712 BCE. So a Julian date value of 2,458,300 would give us 30 June 2018:
select to_char(to_date(2458300, 'j'),'DD-MON-YYYY') from dual;
Knowing that such a number exists for a date might not seem so useful. However once you have a date value, you can then use the to_char function, along with the ‘jsp’ parameter, to spell out the ‘date’ as a number:
select to_char(to_date(2458300, 'j'),'jsp') from dual;
This returns the following result:
two million four hundred fifty-eight thousand three hundred
This means any numeric value can be entered as a number into the above SQL:
select to_char(to_date(105, 'j'),'jsp') from dual;
one hundred five
Strictly speaking, this is not proper English as the ‘jsp’ to_char conversion does not provide you with the connecting ‘and’ in the right place. However, the combination of to_date (Julian), with to_char (jsp) at least gives you a starting point. This could be useful for a quick-and-dirty solution, such as that required for a prototype or a proof of concept.
See Also:
Tip 047: Regular Expressions in SQL
Formats allowed by the ‘Trunc’ function:
https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions230.htm#i1002084