updated 2021-01-06
POSITION()
returns the position of the first occurrence of a substring within a string.
Syntax : POSITION(substr IN str)
Example : SELECT POSITION("ou" IN "w3resource");
Output : 6
Example : SELECT POSITION("w" IN "w3rewind");
Output : 1
LOCATE()
Syntax : LOCATE(substr, str, pos)
returns the position of the first occurrence of a substring within a string.
An optional argument may be used to specify from which position of the string
(i.e. string to be searched) searching will start.
If this position is not mentioned, searching starts from the beginning.
Example : SELECT LOCATE('st', 'myteststring');
Output : 5
SUBSTR()
SUBSTRING()
SUBSTR() is a synonym for SUBSTRING()
Syntax : SUBSTR(str, pos, len)
example: SELECT SUBSTR('w3resource',4,3);
Output : eso
How to check if field is null or empty in mysql
WHERE column IS NULL
OR column = '' ;
WHERE column IS NOT NULL
AND column != ''
SELECT IF(col IS NULL OR col = '', 'empty', col)
FROM table
IFNULL
SELECT contactname, IFNULL(bizphone, homephone) phone
FROM contacts;
get the contact’s home phone if the contact’s business phone is not available.
IFNULL('',1) returns ” because the ” string is not NULL.
ISNULL is the similar function in SQL Server.
Example : ISNULL(saleprice, price)
ISNULL in mysql
ISNULL(bizphone)
If the value (bizphone) is NULL, this function returns 1. Otherwise, this function returns 0.
Math
ex: table “customer-orders” contains fields: quantity, price, & discount
SELECT ROUND (quantity * price * (1-discount), 2) AS SalePrice ;
# to the nearest cent [2 decimal places]
SELECT ROUND (quantity * price * (1-discount)) AS SalePrice ;
# to the nearest dollar [no decimal places = the default]
SELECT ROUND(seconds/60) AS minutes ;
SELECT ROUND(10.5); # result = 11
SELECT ROUND(-10.5); # result = -11
a negative 10.5 [-10.5] rounds to a negative 11 [-11]
SELECT FLOOR(25.75); # result = 25
SELECT FLOOR(-25.75); # result = -26
Return a number truncated to 0 decimal places:
SELECT TRUNCATE(345.567, 0); # result = 345
SELECT TRUNCATE(-345.567, 0); # result = -345
always round up:
SELECT CEILING(25.15); # result = 26
SELECT CEILING(-25.15); # result = -25
CEILING() can be abbreviated to CEIL()
This function is equal to the CEIL() function.
TRIM()
Example : SELECT TRIM(' trim ');
Output : trim (leading and trailing spaces removed)
REPLACE()
Syntax : REPLACE(str, from_str, to_str)
example:
UPDATE book_pages
SET contents = REPLACE(contents,
'What are is a step', 'What is a step')
WHERE contents LIKE '%What are is a step%'
CONCAT()
concat(‘$city’, ‘, ‘, ‘$state’),
example: concat(‘Miami’, ‘, ‘, ‘FL’)
Output : Miami, FL
display names properly capitalized whether they were entered that way or not:
Example : SELECT CONCAT( LEFT(firstname, 1), LOWER(SUBSTRING(firstname, 2)) )
PS:
UCASE and UPPER are the same function.
LCASE and LOWER are the same function.
display all users who upper cased their name etc.
or were too lazy to capitalize it etc.
(add “LOWER” where needed)
SELECT u.id, u.firstname, u.lastname, u.email,
u.phone1, u.address, u.city,
FROM_UNIXTIME(u.lastaccess) AS lastaccess
FROM mdl_user AS u
WHERE UPPER(firstname) = BINARY firstname
OR UPPER(lastname) = BINARY lastname
OR UPPER(email) = BINARY email
OR UPPER(city) = BINARY city
OR LOWER(city) = BINARY city
ORDER BY id DESC
take names that have been entered in ALL CAPS and convert them to properly capitalized names
UPDATE customer
SET firstname = CONCAT( LEFT(firstname, 1), LOWER(SUBSTRING(firstname, 2)) )
WHERE UPPER(firstname) = BINARY firstname
WHERE UPPER(firstname) = BINARY firstname
XOR UPPER(lastname) = BINARY lastname
“or” will include names that are blank
“xor” will not
of course, the other way to exclude blanks is to include where length() > 0
to display all words/names properly capitalized within a field,
a function needs to be written. See a good one at
joezack.com/2008/10/20/mysql-capitalize-function
CONCAT_WS()
Example : SELECT CONCAT_WS('; ', '1st', '2nd', '3rd', '4th', '5th' );
Output : 1st; 2nd; 3rd; 4th; 5th
REGEXP
example: table field contains only letters and numbers
SELECT * FROM table WHERE column REGEXP ‘^[A-Za-z0-9]+$’ ;
or
SELECT * FROM table WHERE column REGEXP ‘^[[:alnum:]]+$’ ;
or
REGEXP ‘^[a-z0-9]+$’ since regexp is not case sensitive except for binary fields.
FORMAT()
Syntax : FORMAT(X, D)
Formats the number X to a format like ‘#,###,###.##’, rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.
price6 = 12332.123456 ;
price1 = 12332.1 ;
Example : SELECT FORMAT(price6, 4);
Output : 12,332.1235
Example : SELECT FORMAT(price1, 4);
Output : 12,332.1000
Example : SELECT FORMAT(price6, 0);
Output : 12,332
LOWER()
Syntax : LOWER(str)
Example : SELECT LOWER(‘MYTESTSTRING’);
Output : myteststring
LCASE()
Syntax : LCASE(str)
converts the characters of a string to lower case characters.
Example : SELECT LCASE(‘MYTESTSTRING’);
Output : myteststring
UPPER()
Syntax : UPPER(str)
Example : SELECT UPPER(‘myteststring’);
Output : MYTESTSTRING
LEFT()
Syntax : LEFT(str,len)
Example : SELECT LEFT(‘w3resource’, 3);
Output : w3r
Example : SELECT concat(left(first, 1), left(middle, 1), left(last, 1) )
Output : the persons 3 initials
LENGTH()
Syntax : LENGTH(str)
Example : SELECT LENGTH(‘text’);
Output : 4
BIT_LENGTH()
Syntax : BIT_LENGTH(str)
Example : SELECT BIT_LENGTH(‘text’);
Output : 32
CHAR_LENGTH()
Syntax : CHAR_LENGTH(str)
Example : SELECT CHAR_LENGTH(‘test string’);
Output : 11
FIND_IN_SET()