{ Ellis Min }

[SQL]: String functions in MySQL

SQL

09/24/2019

MySQL String functions

More documentations can be found from:

Start

Example Data files

Load the sql file

BASH
mysql> source <path>/students.sql;

Test

TEXT
mysql> DESC students;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| students_id | int(11)     | NO   | PRI | NULL    | auto_increment |
| fname       | varchar(50) | YES  |     | NULL    |                |
| lname       | varchar(50) | YES  |     | NULL    |                |
| major       | varchar(50) | YES  |     | NULL    |                |
| age         | int(11)     | YES  |     | NULL    |                |
| credit      | int(11)     | YES  |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)
TEXT
mysql> SELECT * FROM students;
+-------------+----------+---------+------------------+------+--------+
| students_id | fname    | lname   | major            | age  | credit |
+-------------+----------+---------+------------------+------+--------+
|           1 | Ellis    | Min     | Computer Science |   18 |     13 |
|           2 | Tenzin   | Pema    | Biology          |   22 |    317 |
|           3 | Sam      | Smith   | Computer Science |   25 |   1211 |
|           4 | John     | Johnson | Linguistics      |   19 |     21 |
|           5 | Dave     | Smith   | Chemistry        |   23 |     54 |
|           6 | Brent    | Lang    | Accounting       |   22 |     11 |
|           7 | Jerimiah | Tucker  | Business         |   20 |     66 |
|           8 | Lamont   | Marks   | Biochemistry     |   18 |     37 |
|           9 | Diego    | Miller  | Physics          |   17 |      6 |
|          10 | Angelina | Randall | Art              |   26 |     41 |
|          11 | Brad     | Johnson | Biology          |   22 |     13 |
+-------------+----------+---------+------------------+------+--------+
11 rows in set (0.00 sec)

CONCAT

Combine strings(columns) for cleaner input

Example

SQL
SELECT CONCAT('hello', ' world!');
  • Output:
TEXT
mysql> SELECT CONCAT('hello', ' world!');
+----------------------------+
| CONCAT('hello', ' world!') |
+----------------------------+
| hello world!               |
+----------------------------+
1 row in set (0.00 sec)
  • Arguments for CONCAT
    • CONCAT(x, y, z)
    • CONCAT(column, column2)
    • CONCAT(column, 'some text', column, 'moreTexT')
SQL
SELECT CONCAT(fname, ' ', lname)
FROM students;
  • Output:
TEXT
mysql> SELECT CONCAT(fname, ' ', lname)
  -> FROM students;
+---------------------------+
| CONCAT(fname, ' ', lname) |
+---------------------------+
| Ellis Min                 |
| Tenzin Pema               |
| Sam Smith                 |
| John Johnson              |
| Dave Smith                |
| Brent Lang                |
| Jerimiah Tucker           |
| Lamont Marks              |
| Diego Miller              |
| Angelina Randall          |
| Brad Johnson              |
+---------------------------+
11 rows in set (0.00 sec)

Using Alias

SQL
SELECT fname AS full, lname AS last, CONCAT(fname, ' ', lname) AS full
FROM students;
  • Output:
TEXT
+----------+---------+------------------+
| full     | last    | fullname         |
+----------+---------+------------------+
| Ellis    | Min     | Ellis Min        |
| Tenzin   | Pema    | Tenzin Pema      |
| Sam      | Smith   | Sam Smith        |
| John     | Johnson | John Johnson     |
| Dave     | Smith   | Dave Smith       |
| Brent    | Lang    | Brent Lang       |
| Jerimiah | Tucker  | Jerimiah Tucker  |
| Lamont   | Marks   | Lamont Marks     |
| Diego    | Miller  | Diego Miller     |
| Angelina | Randall | Angelina Randall |
| Brad     | Johnson | Brad Johnson     |
+----------+---------+------------------+
11 rows in set (0.00 sec)

CONCAT_WS

  • CONCAT with separator
  • Useful when you concatenate a multiple fields together
SQL
SELECT CONCAT_WS(' - ', major, lname, age)
FROM students;
  • Output:
TEXT
+-------------------------------------+
| CONCAT_WS(' - ', major, lname, age) |
+-------------------------------------+
| Computer Science - Min - 18         |
| Biology - Pema - 22                 |
| Computer Science - Smith - 25       |
| Linguistics - Johnson - 19          |
| Chemistry - Smith - 23              |
| Accounting - Lang - 22              |
| Business - Tucker - 20              |
| Biochemistry - Marks - 18           |
| Physics - Miller - 17               |
| Art - Randall - 26                  |
| Biology - Johnson - 22              |
+-------------------------------------+
11 rows in set (0.00 sec)

SUBSTRING

  • Parts of strings
SQL
SELECT SUBSTRING('hello world!', 1, 4);
  • Display the string from 1st char to 4th char
  • Output:
TEXT
+---------------------------------+
| SUBSTRING('hello world!', 1, 4) |
+---------------------------------+
| hell                            |
+---------------------------------+
1 row in set (0.00 sec)
SQL
SELECT SUBSTRING('hellow world!', 7);
  • Displays string from 7th char and on, inclusive
  • It will display world!
SQL
SELECT SUBSTRING('hello world!', -3);
  • Starting from end of the string, prints out 3 characters
  • It will display ld!

Example with data

SQL
SELECT SUBSTRING(lname, 1, 4) AS shortLname
FROM students;
  • Output:
TEXT
+------------+
| shortLname |
+------------+
| Min        |
| Pema       |
| Smit       |
| John       |
| Smit       |
| Lang       |
| Tuck       |
| Mark       |
| Mill       |
| Rand       |
| John       |
+------------+
11 rows in set (0.00 sec)

SUBSTR()

You can SUBSTR is an alias of SUBSTRING

SQL
SELECT SUBSTR(major, -5)
FROM students;
  • Output:
TEXT
+-------------------+
| SUBSTR(major, -5) |
+-------------------+
| ience             |
| ology             |
| ience             |
| stics             |
| istry             |
| nting             |
| iness             |
| istry             |
| ysics             |
|                   |
| ology             |
+-------------------+
11 rows in set (0.00 sec)
  • Note: Art major is less than 5 characters so it shows an empty string

CONCAT & SUBSTR

SQL
SELECT CONCAT(
    SUBSTR(lname, 1, 3),
    '...'
    ) AS shortLname
FROM students;
  • Output:
TEXT
+------------+
| shortLname |
+------------+
| Min...     |
| Pem...     |
| Smi...     |
| Joh...     |
| Smi...     |
| Lan...     |
| Tuc...     |
| Mar...     |
| Mil...     |
| Ran...     |
| Joh...     |
+------------+
11 rows in set (0.00 sec)

REPLACE

Replace parts of strings

SQL
SELECT REPLACE('hello world!', 'hell', '!@#$')
AS filtered;
  • Replace all occurrences of 2nd argument in 1st argument to 3rd argument
  • They are Case Sensitive
  • Output:
TEXT
+--------------+
| filtered     |
+--------------+
| !@#$o world! |
+--------------+
1 row in set (0.00 sec)

SQL
SELECT REPLACE('get rid of spaces', ' ', '')
AS res;
  • Trim spaces
  • Output:
TEXT
+----------------+
| res            |
+----------------+
| getridofspaces |
+----------------+

Two similar queries

SQL
SELECT REPLACE (
    SUBSTRING(major, 1, 5), 'o', '00') AS res1
FROM students;
SQL
SELECT SUBSTR(
    REPLACE(major, 'o', '00'), 1, 5) AS res2
FROM students;

Output comparison

TEXT
+---------+
| res1    |
+---------+
| C00mpu  |
| Bi00l00 |
| C00mpu  |
| Lingu   |
| Chemi   |
| Acc00u  |
| Busin   |
| Bi00ch  |
| Physi   |
| Art     |
| Bi00l00 |
+---------+
TEXT
+-------+
| res2  |
+-------+
| C00mp |
| Bi00l |
| C00mp |
| Lingu |
| Chemi |
| Acc00 |
| Busin |
| Bi00c |
| Physi |
| Art   |
| Bi00l |
+-------+
  • For example, Computer Science
    • res1 takes COMPU then replaces.
      • C00mpu
    • res2 replaces Computer Science with C00mputer Science then display 5 characters
      • C00mp

TODO

Replace first three 'o's to '00'from major. Leave rest of them intact. Ex) Biology -> Bi00logy

REVERSE

SQL
SELECT REVERSE('hello world!') AS res;
  • Output:
TEXT
+--------------+
| res          |
+--------------+
| !dlrow olleh |
+--------------+
SQL
SELECT CONCAT(lname, REVERSE(lname)) AS palindrome
FROM students;
  • Sandwhiches; make them palindrome
  • Output:
TEXT
+----------------+
| palindrome     |
+----------------+
| MinniM         |
| PemaameP       |
| SmithhtimS     |
| JohnsonnosnhoJ |
| SmithhtimS     |
| LanggnaL       |
| TuckerrekcuT   |
| MarksskraM     |
| MillerrelliM   |
| RandalllladnaR |
| JohnsonnosnhoJ |
+----------------+

CHAR_LENGTH

SQL
SELECT CHAR_LENGTH('hello world!');
  • Output:
TEXT
+-----------------------------+
| CHAR_LENGTH('hello world!') |
+-----------------------------+
|                          12 |
+-----------------------------+
SQL
SELECT major, CHAR_LENGTH(major) AS len
FROM students;
  • Output:
TEXT
+------------------+------+
| major            | len  |
+------------------+------+
| Computer Science |   16 |
| Biology          |    7 |
| Computer Science |   16 |
| Linguistics      |   11 |
| Chemistry        |    9 |
| Accounting       |   10 |
| Business         |    8 |
| Biochemistry     |   12 |
| Physics          |    7 |
| Art              |    3 |
| Biology          |    7 |
+------------------+------+
SQL
SELECT CONCAT(major, ' is ', CHAR_LENGTH(major),' characters long')
AS custom
FROM students;
  • Output:
TEXT
+----------------------------------------+
| custom                                 |
+----------------------------------------+
| Computer Science is 16 characters long |
| Biology is 7 characters long           |
| Computer Science is 16 characters long |
| Linguistics is 11 characters long      |
| Chemistry is 9 characters long         |
| Accounting is 10 characters long       |
| Business is 8 characters long          |
| Biochemistry is 12 characters long     |
| Physics is 7 characters long           |
| Art is 3 characters long               |
| Biology is 7 characters long           |
+----------------------------------------+

UPPER and LOWER

SQL
SELECT UPPER('hello world!');
  • Returns uppercase string of argument
    • LOWER for returning lowercase
  • Output
TEXT
+-----------------------+
| UPPER('hello world!') |
+-----------------------+
| HELLO WORLD!          |
+-----------------------+
SQL
SELECT CONCAT('MY FAVORITE SUBJECT IS ', UPPER(major))
AS FaCT
FROM students
WHERE fname="Ellis";
  • Output:
TEXT
+-----------------------------------------+
| FaCT                                    |
+-----------------------------------------+
| MY FAVORITE SUBJECT IS COMPUTER SCIENCE |
+-----------------------------------------+

Side note

  • UPPER and LOWER takes only one argument
  • Allowed code:
SQL
SELECT UPPER(CONCAT(fname, ' ', lname)) AS fullname
FROM students;
  • NOT allowed:
SQL
SELECT CONCAT(UPPER(fname, ' ', lname)) AS fullname
FROM students;
  • Gives an error:
TEXT
ERROR 1582 (42000): Incorrect parameter count in the call to native function
  • This is allowed but first method would be preferred:
SQL
SELECT CONCAT(UPPER(fname), ' ', UPPER(lname)) AS fullname
FROM students;

Practice

  1. Reverse and make uppercase of string: "Hi How are yOU?"
SQL
SELECT UPPER(REVERSE("Hi How are yOU?"));
  1. Replace spaces in major with '->'
SQL
SELECT REPLACE(major, ' ', '->')
FROM students;
  1. Print out fname in one col, reversed in another col
SQL
SELECT fname AS forward, REVERSE(fname) AS backward
FROM students;
  1. Show full names in uppercase
SQL
SELECT UPPER(
    CONCAT(lname, ', ', fname)
) AS "fullName in caps"
FROM students;
  1. Do Ex) Sam is 25 years old
SQL
SELECT CONCAT(fname, ' is ', age,' years old')
AS blurb
FROM students;
  1. Print fullname and its length
SQL
SELECT CONCAT(fname, ' ', lname) AS fullname,
    CHAR_LENGTH(CONCAT(fname, ' ', lname))
    AS charCount
FROM students;
  1. TODO(sub-query)
SQL
SELECT sub.fullname,
    CHAR_LENGTH(sub.fullname) AS charCount
FROM (
    SELECT CONCAT(s.fname, ' ', s.lname) AS fullname
    FROM students s --DON'T put ; here?
) AS sub;
  • Note: You can't refer to the alias you created in the first SELECT
  1. Print fullname and its length - spaces
SQL
SELECT CONCAT(fname, ' ', lname) AS fullname,
    CHAR_LENGTH(CONCAT(fname, lname)) AS charCount
FROM students;
  1. Provide 3 cols: Compu... Min, Ellis \$13 left in credit
SQL
SELECT CONCAT(SUBSTR(major, 1, 5), '...') AS "short major",
    CONCAT(lname, ', ', fname) AS fname,
    CONCAT('$', credit,' left in credit') AS balance
FROM students;
  • Output:
TEXT
+-------------+-------------------+----------------------+
| short major | fname             | balance              |
+-------------+-------------------+----------------------+
| Compu...    | Min, Ellis        | $13 left in credit   |
| Biolo...    | Pema, Tenzin      | $317 left in credit  |
| Compu...    | Smith, Sam        | $1211 left in credit |
| Lingu...    | Johnson, John     | $21 left in credit   |
| Chemi...    | Smith, Dave       | $54 left in credit   |
| Accou...    | Lang, Brent       | $11 left in credit   |
| Busin...    | Tucker, Jerimiah  | $66 left in credit   |
| Bioch...    | Marks, Lamont     | $37 left in credit   |
| Physi...    | Miller, Diego     | $6 left in credit    |
| Art...      | Randall, Angelina | $41 left in credit   |
| Biolo...    | Johnson, Brad     | $13 left in credit   |
+-------------+-------------------+----------------------+

WRITTEN BY

Ellis Min

Keeping a record

GitHub