# MariaDB String Functions Guide

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.

### Formatting Strings

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

  ```sql
  SELECT CONCAT(name_first, ' ', name_last) AS Name FROM contacts;
  ```

  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

  ```sql
  SELECT CONCAT_WS('|', col1, col2, col3) FROM table1;
  ```

  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

  ```sql
  SELECT CONCAT('$', FORMAT(col5, 2)) AS Price FROM table3;
  ```

  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

  ```sql
  SELECT UCASE(col1) AS Upper_Col1, LCASE(col2) AS Lower_Col2 FROM table4;
  ```

**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

  ```sql
  SELECT RPAD(part_nbr, 8, '.') AS 'Part Nbr.', LPAD(description, 15, '_') AS Description FROM catalog;
  ```

  Example: `RPAD('H200', 8, '.')` might produce `H200....`. `LPAD('hinge', 15, '_')` might produce `__________hinge`.

**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` is the default if no specifier is given before `remstr`. If only `str` is provided, trims leading and trailing spaces.

  ```sql
  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;
  ```

### Extracting Substrings

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`.

  ```sql
  SELECT LEFT(telephone, 3) AS area_code, RIGHT(telephone, 7) AS tel_nbr
  FROM contacts
  ORDER BY area_code;
  ```

  This extracts the first 3 characters as `area_code` and the last 7 as `tel_nbr`.
* `SUBSTRING(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`.

  ```sql
  SELECT CONCAT('(', LEFT(telephone, 3), ') ',
                SUBSTRING(telephone, 4, 3), '-',
                MID(telephone, 7)) AS 'Telephone Number'
  FROM contacts
  ORDER BY LEFT(telephone, 3);
  ```

  This formats a 10-digit phone number like `(504) 555-1234`.

### Manipulating Strings

Functions for changing or generating strings.

* `REPLACE(str, from_str, to_str)`: Replaces all occurrences of `from_str` within `str` with `to_str`.

  ```sql
  SELECT CONCAT(REPLACE(title, 'Mrs.', 'Ms.'), ' ', name_first, ' ', name_last) AS Name
  FROM contacts;
  ```

  This replaces "Mrs." with "Ms." in the `title` column.
* `INSERT(str, pos, len, newstr)`: Replaces the substring in `str` starting at `pos` and `len` characters long with `newstr`. If `len` is 0, `newstr` is inserted at `pos` without overwriting.
* `LOCATE(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.

  ```sql
  -- 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.%';
  ```

  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`.

  ```sql
  SELECT REVERSE('MariaDB'); -- Output: BDeiraM
  ```
* `REPEAT(str, count)`: Repeats `str` `count` times.

  ```sql
  SELECT REPEAT('Ha', 3); -- Output: HaHaHa
  ```

### String Expression Aids

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.

  ```sql
  SELECT COUNT(school_id) AS 'Number of Students'
  FROM table8
  WHERE CHAR_LENGTH(school_id) = 8;
  ```

  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.
* INET\_NTOA(numeric\_ip\_representation): Converts the numeric representation back to an IPv4 address string.

  To correctly sort IP addresses numerically instead of lexically:

  ```sql
  SELECT ip_address
  FROM computers
  WHERE server = 'Y'
  ORDER BY INET_ATON(ip_address)
  LIMIT 3;
  ```

  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`.

  ```sql
  SELECT col1, col2
  FROM table6
  WHERE STRCMP(col3, 'text') = 0; -- Finds exact case-sensitive match for 'text'
  ```
* `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).

  ```sql
  -- 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
  ```

<sub>*This page is licensed: CC BY-SA / Gnu FDL*</sub>

{% @marketo/form formId="4316" %}