The following two functions enable us to get the month name from a date in MySQL.
Continue readingCategory: MySQL
MySQL TIMEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?
This article looks at the difference between the MySQL TIMEDIFF() and TIMESTAMPDIFF() functions.
Both functions do a similar thing, but there are some significant differences between the two.
The following table summarizes the difference between these two functions:
TIMEDIFF() |
TIMESTAMPDIFF() |
|---|---|
| Requires 2 arguments. | Requires 3 arguments. |
| Subtracts the 2nd argument from the 1st (date1 − date2). | Subtracts the 2nd argument from the 3rd (date2 − date1). |
| Result is expressed as a time value (and it has the limitations of the time data type). | Result is an integer, expressed by a number of units as provided by the first argument. |
| Accepts time or datetime expressions. | Accepts date or datetime expressions. |
| Both arguments must be the same type (either time or datetime). | Both arguments can be of a different type (date or datetime). |
MySQL DATEDIFF() vs TIMESTAMPDIFF(): What’s the Difference?
This article looks at the difference between two MySQL functions; DATEDIFF() and TIMESTAMPDIFF().
Both functions return the difference between two dates and/or times, but the result is different between the two functions.
The following table summarizes the difference between these two functions:
DATEDIFF() |
TIMESTAMPDIFF() |
|---|---|
| Requires 2 arguments. | Requires 3 arguments. |
| Subtracts the 2nd argument from the 1st (expr1 − expr2). | Subtracts the 2nd argument from the 3rd (expr2 − expr1). |
| Result is expressed as a value in days. | Result is expressed as the unit provided by the first argument. |
| Can compare only the date value of its arguments. | Can compare the date and time value of its arguments. |
MySQL DATEDIFF() vs TIMEDIFF(): What’s the Difference?
Two date functions included in MySQL are DATEDIFF() and TIMEDIFF().
Both functions do a similar thing, but with some meaningful differences.
The following table summarizes the difference between these two functions:
DATEDIFF() |
TIMEDIFF() |
|---|---|
| Result is expressed as a value in days. | Result is expressed as a time value. |
| Compares only the date value of its arguments. | Compares the time value of its arguments. |
| Accepts date or date-and-time expressions. | Accepts time or date-and-time expressions. |
| Both arguments can be of a different type (date or date-and-time). | Both arguments must be the same type (either time or date-and-time). |
MySQL CHAR() vs T-SQL CHAR(): What’s the Difference?
There are many functions that are included in both MySQL and SQL Server. However, just because both DBMSs share functions of the same name, doesn’t mean that those functions work exactly the same way.
Take CHAR() for example. Both MySQL and SQL Server include this function. Well technically, for SQL Server it’s actually a T-SQL function, but that’s beside the point. The point is that the MySQL CHAR() function provides more functionality than the T-SQL/SQL Server CHAR() function.
In particular, the MySQL version accepts multiple integers, whereas, the T-SQL version only accepts a single integer. The MySQL version also accepts a USING clause that allows you to specify which character set to use (the T-SQL version doesn’t have this clause).
Below are some examples of these differences.
3 Ways to Detect if a String Matches a Regular Expression in MySQL
MySQL has a number of functions and operators that allow us to perform operations using regular expressions (regex). This article presents two operators and one function that enable us to find out if a string matches a regular expression specified by a given pattern.
These regex functions and operators are:
These are all basically equivalent, as the operators (the second two) are both synonyms of the function (the first one). In any case, you can see examples of all three in action below.
How the MATCH() Function Works in MySQL
In MySQL, the MATCH() function performs a full-text search. It accepts a comma separated list of table columns to be searched.
The table/s must have a FULLTEXT index before you can do a full-text search against them (although boolean queries against a MyISAM search index can work — albeit slowly — even without a FULLTEXT index).
You can create a FULLTEXT index when creating the table (using the CREATE TABLE statement), or you can use the ALTER TABLE statement or the CREATE INDEX statement if the table already exists.
By default, the search is case-insensitive. To perform a case-sensitive search, use a case-sensitive or binary collation for the indexed columns.
MySQL String Functions (Full List)
MySQL includes a bunch of functions and operators that can help us when working with data.
The following is a list of string functions and operators available in MySQL. Click on each function or operator name to see an explanation of the function, its syntax, and examples.
How the CHARACTER_LENGTH() Function Works in MySQL
In MySQL, the CHARACTER_LENGTH() function returns the length of a string, measured in characters.
CHARACTER_LENGTH() is a synonym for the CHAR_LENGTH() function.
How the SOUNDS LIKE Operator Works in MySQL
In MySQL, you can use the SOUNDS LIKE operator to return results that sound like a given word.
This operator works best on strings in the English language (using it with other languages may return unreliable results).