MySQL String Functions

Daily Standup

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):

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

    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:

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


  • 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:

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


  • 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):

    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 |


  • 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;


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