MySQL

String Functions

But First...A Helpful Tip

Running SQL files

source file_name.sql

CONCAT

Combine Data For Cleaner Output

+--------------+----------------+
| author_fname | author_lname   |
+--------------+----------------+
| Jhumpa       | Lahiri         |
| Neil         | Gaiman         |
| Neil         | Gaiman         |
| Jhumpa       | Lahiri         |
| Dave         | Eggers         |
| Dave         | Eggers         |
| Michael      | Chabon         |
| Patti        | Smith          |
| Dave         | Eggers         |
| Neil         | Gaiman         |
| Raymond      | Carver         |
| Raymond      | Carver         |
| Don          | DeLillo        |
| John         | Steinbeck      |
| David        | Foster Wallace |
| David        | Foster Wallace |
| Adam         | Smith          |
+--------------+----------------+

What if I want

full names?

🤔

CONCAT(x,y,z)
CONCAT(column, anotherColumn)
CONCAT(author_fname, author_lname)

DaveEggers

JhumpaLahiri

CONCAT(column, 'text', anotherColumn, 'more text')
CONCAT(author_fname, ' ', author_lname)

Dave Eggers

Jhumpa Lahiri

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

CONCAT_WS

Concat With Separator

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

SUBSTRING

Work With Parts Of Strings

SELECT SUBSTRING('Hello World', 1, 4);

Hell

SELECT SUBSTRING('Hello World', 7);

World

SELECT SUBSTRING('Hello World', -3);

rld

SUBSTR() also works

If SUBSTRING is just too much typing for you

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

REPLACE

Replace parts of strings

SELECT REPLACE('Hello World', 'Hell', '%$#@');

%$#@o World

That's a Bad Word 😱

SELECT
  REPLACE('cheese bread coffee milk', ' ', ' and ');

cheese and bread and coffee and milk 

REVERSE

Super Straightforward! 

SELECT REVERSE('Hello World');

dlroW olleH  

That's Pretty Much It....

CHAR_LENGTH

Counts Characters in String

SELECT CHAR_LENGTH('Hello World');

11

UPPER() and LOWER()

Change A String's Case

SELECT UPPER('Hello World');

HELLO WORLD 

SELECT LOWER('Hello World');

hello world  

That was a lot of stuff!

String Function

Exercises! Exercises!

Reverse and Uppercase the following sentence

"Why does my cat look at me with such hatred?"

SELECT
  REPLACE
  (
  CONCAT('I', ' ', 'like', ' ', 'cats'),
  ' ',
  '-'
  );

What Does This Print Out?

+--------------------------------------------------------------+
| title                                                        |
+--------------------------------------------------------------+
| The->Namesake                                                |
| Norse->Mythology                                             |
| American->Gods                                               |
| Interpreter->of->Maladies                                    |
| A->Hologram->for->the->King:->A->Novel                       |
| The->Circle                                                  |
| The->Amazing->Adventures->of->Kavalier->&->Clay              |
| Just->Kids                                                   |
| A->Heartbreaking->Work->of->Staggering->Genius:              |
| Coraline                                                     |
| What->We->Talk->About->When->We->Talk->About->Love:->Stories |
| Where->I'm->Calling->From:->Selected->Stories                |
| White->Noise                                                 |
| Cannery->Row                                                 |
| Oblivion:->Stories                                           |
| Consider->the->Lobster                                       |

Replace spaces in titles with '->'

+----------------+----------------+
| forwards       | backwards      |
+----------------+----------------+
| Lahiri         | irihaL         |
| Gaiman         | namiaG         |
| Gaiman         | namiaG         |
| Lahiri         | irihaL         |
| Eggers         | sreggE         |
| Eggers         | sreggE         |
| Chabon         | nobahC         |
| Smith          | htimS          |
| Eggers         | sreggE         |
| Gaiman         | namiaG         |
| Carver         | revraC         |
| Carver         | revraC         |
| DeLillo        | olliLeD        |
| Steinbeck      | kcebnietS      |
| Foster Wallace | ecallaW retsoF |
| Foster Wallace | ecallaW retsoF |
| Smith          | htimS          |
+----------------+----------------+

Print This Out

+----------------------+
| full name in caps    |
+----------------------+
| JHUMPA LAHIRI        |
| NEIL GAIMAN          |
| NEIL GAIMAN          |
| JHUMPA LAHIRI        |
| DAVE EGGERS          |
| DAVE EGGERS          |
| MICHAEL CHABON       |
| PATTI SMITH          |
| DAVE EGGERS          |
| NEIL GAIMAN          |
| RAYMOND CARVER       |
| RAYMOND CARVER       |
| DON DELILLO          |
| JOHN STEINBECK       |
| DAVID FOSTER WALLACE |
| DAVID FOSTER WALLACE |
| ADAM SMITH           |

You Know What To Do!

+--------------------------------------------------------------------------+
| blurb                                                                    |
+--------------------------------------------------------------------------+
| The Namesake was released in 2003                                        |
| Norse Mythology was released in 2016                                     |
| American Gods was released in 2001                                       |
| Interpreter of Maladies was released in 1996                             |
| A Hologram for the King: A Novel was released in 2012                    |
| The Circle was released in 2013                                          |
| The Amazing Adventures of Kavalier & Clay was released in 2000           |
| Just Kids was released in 2010                                           |
| A Heartbreaking Work of Staggering Genius: was released in 2001          |
| Coraline was released in 2003                                            |
| What We Talk About When We Talk About Love: Stories was released in 1981 |
| Where I'm Calling From: Selected Stories was released in 1989            |
| White Noise was released in 1985                                         |
| Cannery Row was released in 1945                                         |
| Oblivion: Stories was released in 2004                                   |
| Consider the Lobster was released in 2005                                |

Make This Happen!

+-----------------------------------------------------+-----------------+
| title                                               | character count |
+-----------------------------------------------------+-----------------+
| The Namesake                                        |              12 |
| Norse Mythology                                     |              15 |
| American Gods                                       |              13 |
| Interpreter of Maladies                             |              23 |
| A Hologram for the King: A Novel                    |              32 |
| The Circle                                          |              10 |
| The Amazing Adventures of Kavalier & Clay           |              41 |
| Just Kids                                           |               9 |
| A Heartbreaking Work of Staggering Genius:          |              42 |
| Coraline                                            |               8 |
| What We Talk About When We Talk About Love: Stories |              51 |
| Where I'm Calling From: Selected Stories            |              40 |
| White Noise                                         |              11 |
| Cannery Row                                         |              11 |
| Oblivion: Stories                                   |              17 |
| Consider the Lobster                                |              20 | |
+-----------------------------------------------------+-----------------+

Print book titles and the length of each title

+---------------+-------------+--------------+
| short title   | author      | quantity     |
+---------------+-------------+--------------+
| American G... | Gaiman,Neil | 12 in stock  |
| A Heartbre... | Eggers,Dave | 104 in stock |
+---------------+-------------+--------------+

Last One, I Promise!

Refining Selections

More Weapons In The Arsenal

But First...

Some New Books!

INSERT INTO books
    (title, author_fname, author_lname, released_year, stock_quantity, pages)
    VALUES ('10% Happier', 'Dan', 'Harris', 2014, 29, 256), 
           ('fake_book', 'Freida', 'Harris', 2001, 287, 428),
           ('Lincoln In The Bardo', 'George', 'Saunders', 2017, 1000, 367);

DISTINCT

SELECT DISTINCT author_lname FROM books;

What About DISTINCT Full Names??????

🤔

ORDER BY

Sorting Our Results

SELECT author_lname FROM books ORDER BY author_lname;

ASCENDING By Default

But, you can change that...

SELECT author_lname FROM books ORDER BY author_lname DESC;

Numbers Too

SELECT released_year FROM books ORDER BY released_year;
SELECT title, author_fname, author_lname 
FROM books ORDER BY 2;

One Last Thing!

SELECT author_fname, author_lname FROM books 
ORDER BY author_lname, author_fname;

LIMIT

SELECT title, released_year FROM books 
ORDER BY released_year DESC LIMIT 5;
SELECT title, released_year FROM books 
ORDER BY released_year DESC LIMIT 0,5;
SELECT title, released_year FROM books 
ORDER BY released_year DESC LIMIT 5,7;
SELECT * FROM tbl LIMIT 95,18446744073709551615;

LIKE

Better searching

But I can't remember the title!

There's a book I'm looking for...

I know the author's first name is David...

Or wait, maybe it's Dan...or Dave

LIKE

WHERE author_fname LIKE '%da%'

WILDCARDS

LIKE

WHERE author_fname LIKE 'da%'

"I want names that start with da "

LIKE

WHERE stock_quantity LIKE '____'

Yes, that is 4 underscores

But What If...

I'm searching for a book with a '%' in it

I'm searching for a book with an '_' in it

WHERE title LIKE '%\%%'
WHERE title LIKE '%\_%'

Your Turn!

🤡🤡🤡🤡🤡🤡

Select All Story Collections

Titles  That contain 'stories'

+-----------------------------------------------------+
| title                                               |
+-----------------------------------------------------+
| What We Talk About When We Talk About Love: Stories |
| Where I'm Calling From: Selected Stories            |
| Oblivion: Stories                                   |
+-----------------------------------------------------+

Find The Longest Book

Print Out the Title and Page Count

+-------------------------------------------+-------+
| title                                     | pages |
+-------------------------------------------+-------+
| The Amazing Adventures of Kavalier & Clay |   634 |
+-------------------------------------------+-------+
+-----------------------------+
| summary                     |
+-----------------------------+
| Lincoln In The Bardo - 2017 |
| Norse Mythology - 2016      |
| 10% Happier - 2014          |
+-----------------------------+

Print a summary containing the title and year, for the 3 most recent books

Find all books with an author_lname

that contains a space(" ")

+----------------------+----------------+
| title                | author_lname   |
+----------------------+----------------+
| Oblivion: Stories    | Foster Wallace |
| Consider the Lobster | Foster Wallace |
+----------------------+----------------+

Don't Cheat!

Find The 3 Books With The Lowest Stock

Select title, year, and stock

+-----------------------------------------------------+---------------+----------------+
| title                                               | released_year | stock_quantity |
+-----------------------------------------------------+---------------+----------------+
| American Gods                                       |          2001 |             12 |
| Where I'm Calling From: Selected Stories            |          1989 |             12 |
| What We Talk About When We Talk About Love: Stories |          1981 |             23 |
+-----------------------------------------------------+---------------+----------------+
+-----------------------------------------------------+----------------+
| title                                               | author_lname   |
+-----------------------------------------------------+----------------+
| What We Talk About When We Talk About Love: Stories | Carver         |
| Where I'm Calling From: Selected Stories            | Carver         |
| The Amazing Adventures of Kavalier & Clay           | Chabon         |
| White Noise                                         | DeLillo        |
| A Heartbreaking Work of Staggering Genius           | Eggers         |
| A Hologram for the King: A Novel                    | Eggers         |
| The Circle                                          | Eggers         |
| Consider the Lobster                                | Foster Wallace |
| Oblivion: Stories                                   | Foster Wallace |
| American Gods                                       | Gaiman         |
| Coraline                                            | Gaiman         |
| Norse Mythology                                     | Gaiman         |
| 10% Happier                                         | Harris         |
| fake_book                                           | Harris         |
| Interpreter of Maladies                             | Lahiri         |
| The Namesake                                        | Lahiri         |
| Lincoln In The Bardo                                | Saunders       |
| Just Kids                                           | Smith          |
| Cannery Row                                         | Steinbeck      |
+-----------------------------------------------------+----------------+

Print title and author_lname, sorted first by author_lname and then by title

+---------------------------------------------+
| yell                                        |
+---------------------------------------------+
| MY FAVORITE AUTHOR IS RAYMOND CARVER!       |
| MY FAVORITE AUTHOR IS RAYMOND CARVER!       |
| MY FAVORITE AUTHOR IS MICHAEL CHABON!       |
| MY FAVORITE AUTHOR IS DON DELILLO!          |
| MY FAVORITE AUTHOR IS DAVE EGGERS!          |
| MY FAVORITE AUTHOR IS DAVE EGGERS!          |
| MY FAVORITE AUTHOR IS DAVE EGGERS!          |
| MY FAVORITE AUTHOR IS DAVID FOSTER WALLACE! |
| MY FAVORITE AUTHOR IS DAVID FOSTER WALLACE! |
| MY FAVORITE AUTHOR IS NEIL GAIMAN!          |
| MY FAVORITE AUTHOR IS NEIL GAIMAN!          |
| MY FAVORITE AUTHOR IS NEIL GAIMAN!          |
| MY FAVORITE AUTHOR IS FREIDA HARRIS!        |
| MY FAVORITE AUTHOR IS DAN HARRIS!           |
| MY FAVORITE AUTHOR IS JHUMPA LAHIRI!        |
| MY FAVORITE AUTHOR IS JHUMPA LAHIRI!        |
| MY FAVORITE AUTHOR IS GEORGE SAUNDERS!      |
| MY FAVORITE AUTHOR IS PATTI SMITH!          |
| MY FAVORITE AUTHOR IS JOHN STEINBECK!       |
+---------------------------------------------+

Make This Happen...

Sorted Alphabetically By Last Name

Aggregate Functions

Time to learn some more stuff again

It'll be fun. Probably. Hopefully.

COUNT

SELECT COUNT(*) FROM books;

How many books are in the database???!

SELECT COUNT(author_fname) FROM books;

How many author_fnames?

Hmmm....is that really correct though?

SELECT COUNT(DISTINCT author_fname) FROM books;

How many DISTINCT author_fnames?

SELECT COUNT(DISTINCT author_lname) FROM books;

Let's try with author_lname

SELECT COUNT(*) FROM books 
WHERE title LIKE '%the%';

How many titles contain "the"?

GROUP BY

Take A Deep Breathe

Stay with me here...this is a tough one to teach!

"GROUP BY summarizes or aggregates identical data into single rows"

Like I said, stay with me here!

SELECT author_lname FROM books
GROUP BY author_lname
| The Namesake                     | Lahiri       |
| Interpreter of Maladies          | Lahiri       |

| Norse Mythology                  | Gaiman       |
| American Gods                    | Gaiman       |
| A Hologram for the King: A Novel | Eggers       |
+----------------------------------+--------------+
| title                            | author_lname |
+----------------------------------+--------------+
| The Namesake                     | Lahiri       |
| Norse Mythology                  | Gaiman       |
| American Gods                    | Gaiman       |
| Interpreter of Maladies          | Lahiri       |
| A Hologram for the King: A Novel | Eggers       |
+----------------------------------+--------------+

Now the data is grouped, we can do things like...

COUNT how many books each author has written

| The Namesake                     | Lahiri       |
| Interpreter of Maladies          | Lahiri       |

| Norse Mythology                  | Gaiman       |
| American Gods                    | Gaiman       |
| A Hologram for the King: A Novel | Eggers       |
SELECT author_lname, COUNT(*) 
FROM books GROUP BY author_lname;

2

2

1

+----------------+----------+
| author_lname   | COUNT(*) |
+----------------+----------+
| Lahiri         |        2 |
| Gaiman         |        2 |
| Eggers         |        1 |
+----------------+----------+

We're Only Getting Started With GROUP BY

Let's See Some Code!

MIN

AND

MAX

Without Group By

Find the minimum released_year

 

SELECT MIN(released_year) 
FROM books;

Find the longest book

 

SELECT MAX(pages) 
FROM books;

What if I want the title of the longest book?

SELECT MAX(pages), title
FROM books;

This seems like it could work...

🤦‍♂️

The largest emoji slides.com allows for...

One Potential Solution

SELECT * FROM books 
WHERE pages = (SELECT Min(pages) 
                FROM books);

OR

SELECT * FROM books 
ORDER BY pages ASC LIMIT 1;

With Group By

MIN/MAX

 

Find the year each author published their first book

SELECT author_fname, 
       author_lname, 
       Min(released_year) 
FROM   books 
GROUP  BY author_lname, 
          author_fname;

🤷‍♀️🤷‍♂️

Find the longest page count for each author

SELECT
  author_fname,
  author_lname,
  Max(pages)
FROM books
GROUP BY author_lname,
         author_fname;
SELECT
  CONCAT(author_fname, ' ', author_lname) AS author,
  MAX(pages) AS 'longest book'
FROM books
GROUP BY author_lname,
         author_fname;

SUM

Adds Things Together!

Sum all pages in the entire database

SELECT SUM(pages)
FROM books;

SUM + GROUP BY

Sum all pages each author has written

SELECT author_fname,
       author_lname,
       Sum(pages)
FROM books
GROUP BY
    author_lname,
    author_fname;

AVG

Calculate the average released_year across all books

SELECT AVG(released_year) 
FROM books;

Calculate the average stock quantity for books released in the same year

SELECT AVG(stock_quantity) 
FROM books 
GROUP BY released_year;

YOUR
TURN

Print the number of books in the database

Print out how many books were released in each year

Print out the total number of books in stock

Find the average released_year for each author

Find the full name of the author who wrote the longest book

+------+---------+-----------+
| year | # books | avg pages |
+------+---------+-----------+
| 1945 |       1 |  181.0000 |
| 1981 |       1 |  176.0000 |
| 1985 |       1 |  320.0000 |
| 1989 |       1 |  526.0000 |
| 1996 |       1 |  198.0000 |
| 2000 |       1 |  634.0000 |
| 2001 |       3 |  443.3333 |
| 2003 |       2 |  249.5000 |
| 2004 |       1 |  329.0000 |
| 2005 |       1 |  343.0000 |
| 2010 |       1 |  304.0000 |
| 2012 |       1 |  352.0000 |
| 2013 |       1 |  504.0000 |
| 2014 |       1 |  256.0000 |
| 2016 |       1 |  304.0000 |
| 2017 |       1 |  367.0000 |
+------+---------+-----------+

Make This Happen

MySQL 2.0: Refining Selections

By Colt Steele

MySQL 2.0: Refining Selections

  • 37,571

More from Colt Steele