Here are two options we can use to add an option to our sql_mode without wiping all existing options.
Category: MySQL
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.
DROP TABLE IF EXISTS in MySQL
In MySQL, we can use the IF EXISTS clause of the DROP TABLE statement to check whether the table exists or not before dropping it.
Format a Number as Currency in SQL
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 readingConcatenate a String and a Number in SQL
In 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 readingHow to Format Numbers with Commas in MySQL
We can use the FORMAT() function in MySQL to format numbers into a more human readable format.
The function returns the number as a formatted string. It adds thousands separators and a decimal separator as required, and rounds the results to the given decimal position.
Continue reading4 Ways to List All Views in MySQL
Below are four ways to list out the views in a MySQL database using SQL or the command line.
Continue readingFind a String within a String in SQL
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.
How to Check if a Table Already Exists Before Creating it in MySQL
In MySQL, you can use the IF NOT EXISTS clause of the CREATE TABLE statement to check whether or not a table of the same name already exists in the database.
If the table doesn’t exist, it will be created. If it already exists, it won’t be created.
Continue reading