Here are three examples that use SQL to find and select the row with the maximum value in a given column.
The examples work in most major RDBMSs, including MySQL, MariaDB, PostgreSQL, SQLite, Oracle, and SQL Server.
Continue readingCategory: Oracle
2 Ways to Return Rows that Contain Only Alphanumeric Characters in Oracle
Below are two methods for returning rows that only contain alphanumeric characters in Oracle Database.
Alphanumeric characters are alphabetic characters and numeric characters.
Continue readingHow to Format Numbers in Scientific Notation in Oracle
When using the TO_CHAR() function in Oracle Database, you can use the EEEE format element to return the number in scientific notation.
How to Format the Month in Roman Numerals in Oracle
Oracle Database provides us with the ability to return the month from a date using roman numerals.
For example, if the month is August, its decimal value would be 08 and it’s roman numeral equivalent would be VIII.
The way to do this is to use the RM or rm format element in your format mask. That will format the month using roman numerals in uppercase or lowercase respectively.
Specifying the date format can be done in several places.
Continue readingHow to Get Multiple Rows into a Comma Separated List in SQL
Most of the major RDBMSs have a function that enables us to return our query results as a comma separated list.
That is, we can use such a function to convert each row into a separate list item, within a comma separated list.
Below are examples of how to achieve this in some of the more popular RDBMSs.
Continue reading4 Ways to Insert Multiple Rows in Oracle
If you use DBMSs such as MySQL or SQL Server, the syntax for inserting multiple rows into a table with a single statement is quite straightforward.
But if you use Oracle Database, you’ll need to use a different syntax.
Continue readingHow to Remove the Right Padding on the Day Name in Oracle
In Oracle Database, when using the TO_CHAR() function to return the day name from a date, padding will be appended to the day name if it’s shorter than the longest valid day name for the given language and calendar.
Well, that’s the default behaviour. However, you can change this if you wish.
To suppress this padding, all you need to do is prepend the day name format element with fm.
4 Ways to Find Rows that Contain Lowercase Letters in Oracle
Here are four options for returning rows that contain lowercase characters in Oracle Database.
Continue readingList All Triggers in Oracle Database
Oracle Database includes several views that contain information about triggers in the database. We can use these views to return a list of triggers.
Continue readingReturn Rows that Contain Numeric Values in Oracle
The following examples return only those rows that have numeric values in a given column in Oracle Database.
These examples use the REGEXP_LIKE function to return values that match the specified pattern.