When using the TO_CHAR() function to format a number in Oracle Database, you can use the PR format element to return negative values inside angle brackets (<>).
Category: Oracle
Add Leading Zeros in SQL
Below are examples of adding a leading zero to a number in SQL, using various DBMSs.
Continue readingFormat a Number as a Percentage in Oracle
Even though Oracle Database has a TO_CHAR(number) function that allows us to format numbers, it doesn’t provide a format element for the percentage sign.
Therefore, if we want to format a number as a percentage in Oracle Database, we need to concatenate the percentage sign and the number.
Continue readingHow to Export Query Results to a .txt File when using SQLcl (Oracle)
When querying Oracle Database, you can use the SPOOL command to export your query results to a text file when using SQLcl.
How to Remove the Trailing Spaces after the Month Name in Oracle
If you’ve ever used the TO_CHAR() function to format a date that uses the month name, you might have noticed that sometimes the month is returned with right padding.
LISTAGG() Function in Oracle
In Oracle, the LISTAGG() function enables us to combine data from multiple rows in to a single row.
We have the option of specifying a separator (such as a comma). We can also order the results produced by the LISTAGG() function, and more.
How to Remove “X Rows Selected” in SQLcl & SQL*Plus (Oracle)
When using SQLcl or SQL*Plus with Oracle Database, you may have noticed feedback at the bottom of your query results that tells you how many rows were selected. For example, 100 rows selected (or however many rows were returned).
If you want to get rid of this, you can use SET FEEDBACK OFF.
You also have the option of setting a row threshold, which allows you to specify how many rows should be returned before feedback is provided.
Continue readingPadding in SQL
Some RDBMSs provide an LPAD() and RPAD() function that enables us to left pad or right pad a string. Some functions also allow us to add leading or trailing zeros numbers.
Below are examples of applying SQL padding in some of the more popular RDBMSs.
Continue readingHow to Concatenate Strings in SQL
Most of the major RDBMSs provide several options for concatenating two or more strings.
- There’s the
CONCAT()function, which concatenates its arguments. - There’s also a
CONCAT_WS()that allows you to specify a separator that separates the concatenated strings. - And there’s also a string concatenation operator, which allows us to concatenate its operands.
Below are examples of each method.
Continue readingHow to Get the Day, Month, and Year from a Date in SQL
Most of the major RDBMSs have functions that enable us to extract the day, month, and year from datetime values.
Some RDBMSs provide multiple ways to do this, and others are more limited. Below are examples of extracting the day, month, and year from date values in some of the most popular RDBMSs.
Continue reading