Some DBMSs have functions that allow us to format numbers as currency just by passing the appropriate format string. This converts the number to a string with the applicable currency symbol, group separator, and decimal point (if relevant).
Other DBMSs don’t make it that easy, and you need to do a bit of work first.
Below are examples of using SQL to format numbers as currency in some of the most popular DBMSs.
Continue readingIn most cases, concatenating a string and a number in SQL is no different to concatenating two strings.
Most DBMSs will concatenate the string and number as if they were both strings. This is because the number is usually converted to a string before the concatenation operation.
Continue readingYou can use the following views to return a list of views in Oracle Database.
Continue readingSome RDBMSs have a GROUP_CONCAT() function that allows you to return a query column as a delimited list (for example, a comma separated list). MySQL and MariaDB are two that have such a function.
PostgreSQL and SQL Server have similar functions called STRING_AGG().
Oracle, on the other hand, has the LISTAGG() function that does pretty much the same thing (and perhaps more).
So you could say that LISTAGG() is Oracle’s GROUP_CONCAT() equivalent.
When using SQLcl with Oracle Database, you can export your query results to a JSON file with the SPOOL command.
When using the TO_CHAR() function to format a number in Oracle Database, you can use the V format element to a return a value multiplied by 10n (and if necessary, round it up), where n is the number of 9s after the V.
Most of the major DBMSs provide us with a way to find a string within a string using SQL. By this, I mean use a SQL query to find the position of a substring within a string.
There are several SQL functions that allow us to do this, including INSTR(), LOCATE(), POSITION(), and CHARINDEX(). The function you use will depend on your DBMS, and possibly whether or not you need to specify a starting position.
Most major RDBMSs provide ways for us to format numbers using SQL. Some provide only basic formatting capabilities, while others are more advanced.
Below are examples of formatting a number in some of the more popular RDBMSs.
Continue readingWhen formatting dates in Oracle Database, we have the option of using RR and YY to return a two digit year.
These two format elements are similar. The difference is in how they interpret two digits years.
We also have the option of using RRRR and YYYY when returning four digit years.
When using SQLcl with Oracle Database, you can set SQLFORMAT to insert in order to output the query results as INSERT statements.