Example
Return the year and week number for a date:
SELECT YEARWEEK("2017-06-15");
Definition and Usage
The YEARWEEK() function returns the year and week number (a number from 0 to 53) for a given date.
Syntax
YEARWEEK(date, firstdayofweek)
Tag: mysql where example
MySQL SUBDATE Function
Example
Subtract 10 days from a date and return the date:
SELECT SUBDATE("2017-06-15", INTERVAL 10 DAY);
Definition and Usage
The SUBDATE() function subtracts a time/date interval from a date and then returns the date.
Syntax
SUBDATE(date, INTERVAL value unit)
OR:
SUBDATE(date, days)
MySQL MONTHNAME Function
Example
Return the name of the month for a date:
SELECT MONTHNAME("2017-06-15");
Definition and Usage
The MONTHNAME() function returns the name of the month for a given date.
Syntax
MONTHNAME(date)
MySQL LOCALTIME Function
Example
Return current date and time:
SELECT LOCALTIME();
Definition and Usage
The LOCALTIME() function returns the current date and time.
Note: The date and time is returned as “YYYY-MM-DD HH-MM-SS” (string) or as YYYYMMDDHHMMSS.uuuuuu (numeric). Continue reading MySQL LOCALTIME Function
MySQL DAYOFYEAR Function
Example
Return the day of the year for a date:
SELECT DAYOFYEAR("2017-06-15");
Definition and Usage
The DAYOFYEAR() function returns the day of the year for a given date (a number from 1 to 366). Continue reading MySQL DAYOFYEAR Function
MySQL LN Function
Example
Return the natural logarithm of 2:
SELECT LN(2);
Definition and Usage
The LN() function returns the natural logarithm of a number.
Note: See also the LOG() and EXP() functions. Continue reading MySQL LN Function
MySQL MID Function
Example
Extract a substring from a string (start at position 5, extract 3 characters):
SELECT MID("SQL Tutorial", 5, 3) AS ExtractString;
Definition and Usage
The MID() function extracts a substring from a string (starting at any position).
Note: The position of the first character in the string is 1.
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 |
| S | Q | L | T | u | t | o | r | i | a | l |
Note: The position of the last character in the string is -1.
| -12 | -11 | -10 | -9 | -8 | -7 | -6 | -5 | -4 | -3 | -2 | -1 |
| S | Q | L | T | u | t | o | r | i | a | l |
Note: The MID() and SUBSTR() functions equals the SUBSTRING() function. Continue reading MySQL MID Function
MySQL LOCATE Function
Example
Search for “3” in string “Iampsp.com”, and return position:
SELECT LOCATE("3", "Iampsp.com") AS MatchPosition;
Definition and Usage
The LOCATE() function returns the position of the first occurrence of a substring in a string.
If the substring is not found within the original string, this function returns 0.
This function performs a case-insensitive search.
Note: This function is equal to the POSITION() function.
Syntax
LOCATE(substring, string, start)
Parameter Values
| Parameter | Description |
|---|---|
| substring | Required. The substring to search for in string |
| string | Required. The string that will be searched |
| start | Optional. The starting position for the search. Position 1 is default |
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Search for “com” in string “Iampsp.com” (start at position 3), and return position :
SELECT LOCATE("com", "Iampsp.com", 3) AS MatchPosition;
Example
Search for “a” in CustomerName column, and return position:
SELECT LOCATE("a", CustomerName)
FROM Customers;
MySQL FIND_IN_SET Function
Example
Search for “q” within the list of strings:
SELECT FIND_IN_SET("q", "s,q,l");
Definition and Usage
The FIND_IN_SET() function returns the position of a string within a list of strings.
Syntax
FIND_IN_SET(string, string_list)
Parameter Values
| Parameter | Description |
|---|---|
| string | Required. The string to search for |
| string_list | Required. The list of string values to be searched (separated by commas) |
Return Values
- If string is not found in string_list, this function returns 0
- If string or string_list is NULL, this function returns NULL
- If string_list is an empty string (“”), this function returns 0
Technical Details
| Works in: | From MySQL 4.0 |
|---|
More Examples
Example
Search for “a” within the list of strings:
SELECT FIND_IN_SET("a", "s,q,l");
Example
Search for “q” within the list of strings (string list is NULL):
SELECT FIND_IN_SET("q", null);