This guide goes through several built-in string functions in MariaDB, grouping them by similar features, and providing examples of how they might be used.
This guide explores a variety of MariaDB's built-in string functions essential for effective data manipulation. Learn how to format text for display, extract specific substrings, replace content, and utilize various expression aids to enhance your string operations in SQL queries.
Several functions are available for formatting text and numbers for display or processing.
Concatenating Strings:
CONCAT(str1, str2, ...): Joins two or more strings together.
SQL
This displays a full name by combining name_first, a space, and name_last.
CONCAT_WS(separator, str1, str2, ...): Joins strings with a specified separator between each.
SQL
This creates a pipe-delimited string from col1, col2, and col3.
Formatting Numbers:
FORMAT(number, decimal_places): Formats a number with commas every three digits and a specified number of decimal places.SQL
This prepends a dollar sign to a number formatted with commas and two decimal places (e.g., $100,000.00).
Changing Case:
UCASE(str) or UPPER(str): Converts a string to all upper-case letters.
LCASE(str) or LOWER(str): Converts a string to all lower-case letters.SQL
Padding Strings:
LPAD(str, len, padstr): Left-pads str with padstr until it is len characters long.
RPAD(str, len, padstr): Right-pads str with padstr until it is len characters long.SQL
Trimming Strings:
LTRIM(str): Removes leading spaces.
RTRIM(str): Removes trailing spaces.
TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str): Removes leading, trailing, or both occurrences of remstr (or spaces if remstr is not given). BOTH
These functions help extract specific parts of a string.
LEFT(str, len): Returns the leftmost len characters from str.
RIGHT(str, len): Returns the rightmost len characters from str.
This extracts the first 3 characters as area_code and the last 7 as tel_nbr
Functions for changing or generating strings.
REPLACE(str, from_str, to_str): Replaces all occurrences of from_str within str with to_str.
This replaces "Mrs." with "Ms." in the title column.
INSERT(str, pos, len, newstr): Replaces the substring in str starting at
Functions that provide information about strings or assist in specific comparisons/conversions.
CHAR_LENGTH(str) or CHARACTER_LENGTH(str): Returns the length of str in characters.
This counts rows where school_id has exactly 8 characters.
INET_ATON(ip_address_str): Converts an IPv4 address string (e.g., '10.0.1.1') into a numeric representation suitable for numeric sorting.
This page is licensed: CC BY-SA / Gnu FDL
RPAD('H200', 8, '.') might produce H200..... LPAD('hinge', 15, '_') might produce __________hinge.remstrstrSUBSTRING(str, pos, [len]) or MID(str, pos, [len]): Returns a substring len characters long from str, starting at position pos. MID() is a synonym for SUBSTRING(). If len is omitted, returns the rest of the string from pos.
This formats a 10-digit phone number like (504) 555-1234.
poslennewstrlennewstrposLOCATE(substr, str, [pos]): Returns the starting position of the first occurrence of substr within str. An optional pos specifies where to start searching. Returns 0 if substr is not found.
This finds 'Mrs.' in the name string, and replaces it with 'Ms.'. LENGTH('Mrs.') (which is 4) is used for len. If LOCATE() returns 0, INSERT() with a position of 0 typically returns the original string unchanged.
REVERSE(str): Reverses the characters in str.
REPEAT(str, count): Repeats str count times.
INET_NTOA(numeric_ip_representation): Converts the numeric representation back to an IPv4 address string.
To correctly sort IP addresses numerically instead of lexically:
Lexical sort of 10.0.1.1, 10.0.11.1, 10.0.2.1 might be 10.0.1.1, 10.0.11.1, 10.0.2.1.
Numeric sort (using INET_ATON) would correctly be 10.0.1.1, 10.0.2.1, 10.0.11.1.
STRCMP(str1, str2): Performs a case-sensitive comparison of str1 and str2.
Returns 0 if strings are identical.
Returns -1 if str1 is alphabetically before str2.
Returns 1 if str1 is alphabetically after str2.
SUBSTRING_INDEX(str, delim, count): Returns a substring from str before or after count occurrences of the delimiter delim.
If count is positive, returns everything to the left of the count-th delimiter (from the left).
If count is negative, returns everything to the right of the abs(count)-th delimiter (from the right).
SELECT CONCAT('(', LEFT(telephone, 3), ') ',
SUBSTRING(telephone, 4, 3), '-',
MID(telephone, 7)) AS 'Telephone Number'
FROM contacts
ORDER BY LEFT(telephone, 3);-- Example: Change 'Mrs.' to 'Ms.' where title is embedded in a 'name' column
SELECT INSERT(name, LOCATE('Mrs.', name), LENGTH('Mrs.'), 'Ms.')
FROM contacts
WHERE name LIKE '%Mrs.%';SELECT REVERSE('MariaDB'); -- Output: BDeiraMSELECT REPEAT('Ha', 3); -- Output: HaHaHaSELECT ip_address
FROM computers
WHERE server = 'Y'
ORDER BY INET_ATON(ip_address)
LIMIT 3;SELECT CONCAT(name_first, ' ', name_last) AS Name FROM contacts;SELECT CONCAT_WS('|', col1, col2, col3) FROM table1;SELECT CONCAT('$', FORMAT(col5, 2)) AS Price FROM table3;SELECT UCASE(col1) AS Upper_Col1, LCASE(col2) AS Lower_Col2 FROM table4;SELECT RPAD(part_nbr, 8, '.') AS 'Part Nbr.', LPAD(description, 15, '_') AS Description FROM catalog;SELECT LEFT(telephone, 3) AS area_code, RIGHT(telephone, 7) AS tel_nbr
FROM contacts
ORDER BY area_code;SELECT CONCAT(REPLACE(title, 'Mrs.', 'Ms.'), ' ', name_first, ' ', name_last) AS Name
FROM contacts;SELECT COUNT(school_id) AS 'Number of Students'
FROM table8
WHERE CHAR_LENGTH(school_id) = 8;SELECT
TRIM(LEADING '.' FROM col1) AS Trimmed_Leading_Dots,
TRIM(TRAILING FROM col2) AS Trimmed_Trailing_Spaces, -- Trims spaces
TRIM(BOTH '_' FROM col3) AS Trimmed_Both_Underscores,
TRIM(col4) AS Trimmed_Spaces -- Trims leading and trailing spaces
FROM table5;SELECT col1, col2
FROM table6
WHERE STRCMP(col3, 'text') = 0; -- Finds exact case-sensitive match for 'text'-- Get the first two elements from a pipe-delimited string
SELECT SUBSTRING_INDEX('elem1|elem2|elem3|elem4', '|', 2); -- Output: elem1|elem2
-- Get the last two elements
SELECT SUBSTRING_INDEX('elem1|elem2|elem3|elem4', '|', -2); -- Output: elem3|elem4