MySQL String Functions

Kept working on the MySQL course. First we finished up with basic CRUD commands which I added to yesterday’s post for easier reference.

Running SQL Files

Until now we’ve been doing all of the commands in the command line, but now we will be writing them in a .sql file going forward. Then you can run a file containing many commands at once from the MySQL command line with source file.sql. The root directory is whatever directory we’re in when we open the MySQL CLI, so sometimes the file path will need to be included, for example: source inserts/test.sql.

MySQL String Functions

Next up was getting into string functions. When queries start getting long there are some SQL tools which can help make it look better like SQL Format. Here’s an overview of the string functions covered today:

CONCAT (x, y, z) & CONCAT_WS

Concatenates x y and z together.
If not part of a table: SELECT CONCAT('Hello', 'World'); // HelloWorld
Or…SELECT CONCAT('Hello', '...', 'World'); // Hello…World
…notice that spaces have to be included as an argument of CONCAT

Examples to create new column (and optionally rename it):

SELECT
  CONCAT(author_fname, ' ', author_lname)
FROM books;

SELECT
  CONCAT(author_fname, ' ', author_lname)
  AS 'full name'
FROM books;

SELECT author_fname AS first, author_lname AS last,
  CONCAT(author_fname, ' ', author_lname) AS full
FROM books;

CONCAT_WS concatenates with a separator between each field:

SELECT
    CONCAT_WS(' - ', title, author_fname, author_lname)
FROM books;

SUBSTRING

Substring gives you a portion of the string you identify
Note, indexing starts at 1 not 0!
Two numbers goes from 1st index to 2nd: SELECT SUBSTRING('Hello World', 1, 4); // Hell
One number goes from this index to end: SELECT SUBSTRING('Hello World', 7); // World
Negative number goes from end, that many indices: SELECT SUBSTRING('Hello World', -3); // rld
Use with column names to output new column of substrings (alias optional): SELECT SUBSTRING(title, 1, 10) AS 'short title' FROM books;
SUBSTR() is a valid alternate: SELECT SUBSTR(title, 1, 10) AS 'short title' FROM books;

String functions can be combined. For example:

SELECT CONCAT
    (
        SUBSTRING(title, 1, 10),
        '...'
    ) AS 'short title'
FROM books;

…result:

+---------------+
| short title   |
+---------------+
| The Namesa... |
| Norse Myth... |
| American G... |
| Interprete... |
| A Hologram... |
| The Circle... |
| The Amazin... |
| Just Kids...  |
| A Heartbre... |
| Coraline...   |
| What We Ta... |
| Where I'm ... |
| White Nois... |
| Cannery Ro... |
| Oblivion: ... |
| Consider t... |
+---------------+

REPLACE

The format: SELECT REPLACE('original string', 'what you want to replace', what you want to replace it with);
Example: SELECT REPLACE('Hello World', 'Hell', '%$#@'); // %$#@o World
Example: SELECT REPLACE('Hello World', 'o', '0'); // Hell0 W0rld
It’s case-sensitive: SELECT REPLACE('HellO World', 'o', '#'); //HellO W#rld
Also apples to whole columns: SELECT REPLACE(title, 'e ', '3') FROM books;

Can be combined with other string functions (and optionally aliased):

SELECT
    SUBSTRING(REPLACE(title, 'e', '3'), 1, 10) AS 'weird string'
FROM books;

Result:

+--------------+
| weird string |
+--------------+
| Th3 Nam3sa   |
| Nors3 Myth   |
| Am3rican G   |
| Int3rpr3t3   |
| A Hologram   |
| Th3 Circl3   |
| Th3 Amazin   |
| Just Kids    |
| A H3artbr3   |
| Coralin3     |
| What W3 Ta   |
| Wh3r3 I'm    |
| Whit3 Nois   |
| Cann3ry Ro   |
| Oblivion:    |
| Consid3r t   |
+--------------+

REVERSE

Does what it says on the tin.
SELECT REVERSE('Hello World'); // dlroW olleH
Can be combined with other functions: SELECT CONCAT('woof', REVERSE('woof')); // wooffoow
Can be called on table columns: SELECT CONCAT(author_fname, REVERSE(author_fname)) FROM books;

CHAR_LENGTH

Returns character length of what you ask for.
SELECT CHAR_LENGTH('Hello World'); // 11
Can be combined with other functions: SELECT CONCAT(author_lname, ' is ', CHAR_LENGTH(author_lname), ' characters long') FROM books;

Changing String Case

SELECT UPPER converts everything to upper case
SELECT LOWERconverts everything to lower case
SELECT UPPER('Hello World'); // HELLO WORLD
SELECT CONCAT('MY FAVORITE BOOK IS ', LOWER(title)) FROM books; returns column of titles with all of the data in lower case

Other Stuff

Really not happy with AWS and how CloudFront is serving my site. No matter what I set the cache time limit to be, it doesn’t pull an updated version of the home index.html! Really don’t want that while the blog is still sitting on the home page…I’m planning to change that soon so maybe I will go back, but for now I’m going to move the site off of AWS and go back to GitHub Pages.

Up Next

Getting through MySQL a lot faster than I thought I would, so will keep up with that until finished! Although I haven’t given up on CS50—I’ve allocated some big chunks for that over the weekend to make sure I finish the course.