In SQL, the columns information schema view, which returns information about columns, is an ISO standard view that is supported by most of the major RDBMSs. You can use this view to get information about a column’s data type.
Most of the major RDBMs also provide other methods for getting column information.
Here are examples of getting the data type of a column in various SQL databases.
Continue readingIn MySQL, the SCHEMA() function is a synonym for the DATABASE() function. It returns the default (current) database name.
The result is returned as a string in the utf8 character set. If there is no default database, it returns NULL.
In MySQL, you can run a query like the following to return non-numeric data from the column.
This can be helpful if you ever find a column that contains numeric data, but it was set up as a varchar or char column. You can use this query to find any non-numeric values that might have been inserted into the column. You can then deal with it any way you like, such as convert them to numeric, then change the column type.
Here are four ways to get the data type of a column in MySQL.
Continue readingMost of the major RDBMSs have functions that enable us to format numbers with commas as either the group separator, or the decimal separator.
Some RDBMSs also output certain numeric data types with commas in the relevant place.
Below are examples of formatting numbers with commas in some of the more popular DBMSs.
Continue readingIn MySQL, DATABASE() is a built-in function that returns the default (current) database name.
The result is returned as a string in the utf8 character set. If there is no default database, it returns NULL.
In MySQL, the SUM() aggregate function returns the sum of a given expression.
It can also be used to return the sum of all distinct (unique) values in an expression.
Continue readingThere are several ways to format a number as a percentage in SQL, depending on the DBMS being used.
Here are examples of adding a percent sign to a number in some of the most popular DBMSs.
Continue readingHere are two options we can use to add an option to our sql_mode without wiping all existing options.
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).