In MariaDB, MAX() is an aggregate function that returns the maximum value in a given expression.
Tag: what is
SQL LPAD()
In SQL, LPAD()is a commonly used function that pads the left part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an LPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an LPAD() function include SQL Server and SQLite (although there are other ways to apply left padding in these DBMSs).
SQL RPAD()
In SQL, RPAD()is used to pad the right part of a string with a specified character. The function can be used on strings and numbers, although depending on the DBMS, numbers may have to be passed as a string before they can be padded.
DBMSs that have an RPAD() function include MySQL, MariaDB, PostgreSQL, and Oracle.
DBMSs that don’t have an RPAD() function include SQL Server and SQLite.
Oracle GROUP_CONCAT() Equivalent
Some 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.
About the V Format Element in Oracle
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.
RR vs YY in Oracle
When 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.
JSON_VALUE() in MySQL
In MySQL, the JSON_VALUE() function extracts a value from a JSON document at the specified path.
The function was introduced in MySQL 8.0.21.
Continue readingINSTR() Equivalent in SQL Server
Many RDBMSs have an INSTR() function that enables us to find a substring within a string. Some (such as MySQL and MariaDB) also have a LOCATE() function and a POSITION() function (also supported by PostgreSQL), that do a similar thing.
SQL Server doesn’t have an INSTR() function. Nor does it have a LOCATE() or POSITION() function. But it does have the CHARINDEX() function that does the same thing.
SQL Server also has the PATINDEX() function, which does a similar job to CHARINDEX().
GROUP_CONCAT() Function in MySQL
MySQL has a GROUP_CONCAT() function that enables us to return columns from a query as a delimited list.
It returns a string result with the concatenated non-NULL values from a group.
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.