MySQL String Functions๏ƒ

CONCAT๏ƒ

Concatenate two or more strings into one.

SELECT 'MySQL ' 'String ' 'Concatenation';
SELECT CONCAT('MySQL','CONCAT');
SELECT CONCAT('MySQL',NULL,'CONCAT');
SELECT concat(contactFirstName,' ',contactLastName) Fullname FROM customers;
SELECT CONCAT_WS(',','John','Doe');
SELECT CONCAT_WS(NULL ,'Jonathan', 'Smith');
SELECT CONCAT_WS(',','Jonathan', 'Smith',NULL);
SELECT CONCAT_WS(CHAR(13),CONCAT_WS(' ', contactLastname, contactFirstname),addressLine1,addressLine2,CONCAT_WS(' ', postalCode, city),country,CONCAT_WS(CHAR(13), '')) AS Customer_Address FROM customers;

LENGTH & CHAR_LENGTH๏ƒ

Get the length of a string in bytes and in characters.

SHOW CHARACTER SET;
SET @s = CONVERT('MySQL String Length' USING ucs2);
SELECT CHAR_LENGTH(@s), LENGTH(@s);
SET @s = CONVERT('MySQL string length' USING latin1);
SELECT LENGTH(@s), CHAR_LENGTH(@s);
SET @s = CONVERT('MySQL string length' USING utf8);
SELECT LENGTH(@s), CHAR_LENGTH(@s);
SET @s = CONVERT('รก' USING utf8);
SELECT CHAR_LENGTH(@s), LENGTH(@s);
CREATE TABLE posts(
  postid int auto_increment primary key,
  title varchar(255) NOT NULL,
  excerpt varchar(255) NOT NULL,
  content text,
  pubdate datetime
)Engine=InnoDB;
INSERT INTO posts(title,excerpt,content)
VALUES('MySQL Length','MySQL string length function tutorial','dummy'),
      ('Second blog post','Second blog post','dummy');
SELECT postid,title,IF(CHAR_LENGTH(excerpt) > 20,CONCAT(LEFT(excerpt,20), '...'),excerpt) summary FROM posts;

LEFT๏ƒ

Get the left part of a string with a specified length.

SELECT LEFT('MySQL LEFT', 5);
SELECT LEFT('MySQL LEFT', 9999);
SELECT LEFT('MySQL LEFT', 0);
SELECT LEFT('MySQL LEFT', -2);
SELECT LEFT('MySQL LEFT', NULL);
SELECT productname, LEFT(productDescription, 50) summary FROM products;
SELECT LEFT(productdescription, 50) FROM products;
SELECT REVERSE(LEFT(productdescription, 50)) FROM products;
SELECT LOCATE(' ',REVERSE(LEFT(productdescription, 50))) first_space_pos FROM products;
SELECT IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0) FROM products;
SELECT productDescription,(50 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0)) last_space_pos FROM products;
SELECT productDescription, LEFT(productDescription, last_space_pos) FROM (SELECT productDescription,(50 - IFNULL(NULLIF(LOCATE(' ', REVERSE(LEFT(productDescription, 50))), 0) - 1, 0)) last_space_pos FROM products) AS t;

REPLACE๏ƒ

Search and replace a substring in a string.

UPDATE products SET productDescription = REPLACE(productDescription,'abuot','about');

SUBSTRING๏ƒ

Extract a substring starting from a position with a specific length.

SELECT SUBSTRING('MYSQL SUBSTRING', 7);
SELECT SUBSTRING('MySQL SUBSTRING',-10);
SELECT SUBSTRING('MYSQL SUBSTRING', 0);
SELECT SUBSTRING('MySQL SUBSTRING' FROM -10);
SELECT SUBSTRING('MySQL SUBSTRING',1,5);
SELECT SUBSTRING('MySQL SUBSTRING' FROM 1 FOR 5);
SELECT SUBSTRING('MySQL SUBSTRING',-15,5);
SELECT SUBSTRING('MySQL SUBSTRING' FROM -15 FOR 5);

TRIM๏ƒ

Remove unwanted characters from a string.

SELECT TRIM(' MySQL TRIM Function ');
SELECT TRIM(LEADING FROM '    MySQL TRIM Function   ');
SELECT TRIM(TRAILING FROM '    MySQL TRIM Function   ');
UPDATE products SET productname = TRIM(productname);
SELECT LTRIM('  MySQL LTRIM function');
SELECT RTRIM('MySQL RTRIM function   ');

FIND_IN_SET๏ƒ

Find a string within a comma-separated list of strings.

SELECT FIND_IN_SET('y','x,y,z'); -- 2
SELECT FIND_IN_SET('a','x,y,z');
SELECT FIND_IN_SET('a','');
SELECT FIND_IN_SET(NULL,'x,y,z');
SELECT FIND_IN_SET('a',NULL);
CREATE TABLE IF NOT EXISTS divisions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(25) NOT NULL,
    belts VARCHAR(200) NOT NULL
);
INSERT INTO divisions(name,belts)
VALUES ('O-1','white,yellow,orange'),
 ('O-2','purple,green,blue'),
 ('O-3','brown,red,black'),
 ('O-4','white,yellow,orange'),
 ('O-5','purple,green,blue'),
 ('O-6','brown,red'),
 ('O-7','black'),
 ('O-8','white,yellow,orange'),
 ('O-9','purple,green,blue'),
 ('O-10','brown,red');
SELECT name, belts FROM divisions WHERE FIND_IN_SET('red', belts);
SELECT name, belts FROM divisions WHERE NOT FIND_IN_SET('black', belts);
SELECT name, belts FROM divisions WHERE name IN ('O-1' , 'O-2');
SELECT name, belts FROM divisions WHERE FIND_IN_SET(name, 'O-1,O-2');

FORMAT๏ƒ

Format a number with a specific locale, rounded to the number of decimals

SELECT FORMAT(12500.2015, 2);
SELECT FORMAT(12500.2015, 0);
SELECT FORMAT(12500.2015, 2,'de_DE');
SELECT productname, quantityInStock * buyPrice stock_value FROM products;
SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products;
SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products ORDER BY stock_value;
SELECT productname,CONCAT('$',FORMAT(quantityInStock * buyPrice, 2)) stock_value FROM products ORDER BY quantityInStock * buyPrice;

References๏ƒ