Below are examples of using SQL to return the first day of the month across various DBMSs.
This could be the first day of the current month, or the first day of a month based on a given date.
Continue readingCategory: MySQL
How to Delete Duplicate Rows that have a Primary Key in SQL
The following examples use SQL to delete duplicate rows that have a primary key or unique identifier column.
Specifically, the examples delete all duplicates except one. So for example, in the case of three identical rows, it deletes two of them and keeps one.
Continue readingReturn Rows that Contain Only Non-Alphanumeric Characters in SQL
Here are examples of using SQL to return rows that contain non-alphanumeric characters in various DBMSs.
Non-alphanumeric characters include punctuation characters like !@#&()–[{}]:;',?/* and symbols like `~$^+=<>“, as well as whitespace characters like the space or tab characters.
Return Only Numeric Values in SQL
If you ever need to use SQL to return just the numeric values in a character column, the method you use will largely depend on your DBMS. Most DBMSs support regular expressions (regex), but some don’t. And some include functions that can detect numeric values.
Here are examples of how to do it in the more popular DBMSs.
Continue readingFind Rows that Contain Uppercase Characters in SQL
Most RDBMSs provide at least a few ways to return rows that contain uppercase characters. Here are some options available in the major RDBMSs.
Continue readingHow to Get the Last Day of the Month in SQL
Below are examples of using SQL to return the last day of the month across various DBMSs.
This could be the last day of the current month, or the last day of a month based on a given date.
Continue readingFind Duplicate Rows that have a Primary Key in SQL
Here are examples of using SQL to return duplicate rows when those rows have a primary key or other unique identifier column.
These queries work in most of the major RDBMSs, including SQL Server, Oracle, MySQL, MariaDB, PostgreSQL, and SQLite.
Continue readingFind Rows that Contain Lowercase Characters in SQL
Most RDBMSs provide at least a few ways to return rows that contain lowercase characters. Here are some options available in the major RDBMSs.
Continue readingSelect Duplicate Rows in SQL
Here’s an example of using SQL to find duplicate rows in a database table. This technique can be used in most of the major RDBMSs, including SQL Server, Oracle, MySQL, MariaDB, PostgreSQL, and SQLite.
Continue reading2 Ways to Get the Definition of a Generated Column in MySQL
A generated column is one whose value is derived from an expression, as opposed to a fixed value. The expression typically uses other columns in the same table to compute the derived value.
We can create a generated column in MySQL very easily. But what if we want to go back later and see its definition?
Here are two options for returning the definition of a generated column in MySQL.
Continue reading