MySQL has many built in functions for dealing with strings. Many times, we can perform these operations using whatever programming language we happen to be using with MySQL such as PHP. Other times however, it is really helpful to be able to manipulate strings right in our MySQL statements. In this episode we’ll take a look at working with strings and doing things like setting them to all upper or lowercase, reversing their order, replacing characters with other characters, gluing strings together with concatenation, and much more. Let’s get right to it!
Strings in MySQL
Likely if you’re reading an article on this website, you are familiar with PHP and it’s many php string functions. MySQL also has some useful ways to operate on strings. Let’s look at a few of them now.
Using the lower and upper functions
When we make a select query on the database, we can apply a lower or upper function to string data. In this example, we’ll get all the first names of our friends in lower case with their last names in uppercase.
mysql> select lower(first_name), upper(first_name) from friends; +-------------------+-------------------+ | lower(first_name) | upper(first_name) | +-------------------+-------------------+ | brenda | BRENDA | | dave | DAVE | | emmet | EMMET | | frankie | FRANKIE | | jack | JACK | | jacob | JACOB | | jim | JIM | | mary | MARY | | sheila | SHEILA | | sofia | SOFIA | +-------------------+-------------------+ 10 rows in set (0.04 sec)
Repeating String with MySQL
Though it may seem like a strange use case, you can cause a string to be repeated in MySQL. The repeat() function takes the string to be repeated as the first parameter followed by the number of times to repeat it as the second parameter. Here is an example:
mysql> select repeat('ohyeah! ', 3);
+--------------------------+
| repeat('ohyeah! ', 3) |
+--------------------------+
| ohyeah! ohyeah! ohyeah! |
+--------------------------+
1 row in set (0.04 sec)
mysql> select repeat(first_name, 4) from friends;
+------------------------------+
| repeat(first_name, 4) |
+------------------------------+
| BrendaBrendaBrendaBrenda |
| DaveDaveDaveDave |
| EmmetEmmetEmmetEmmet |
| FrankieFrankieFrankieFrankie |
| JackJackJackJack |
| JacobJacobJacobJacob |
| JimJimJimJim |
| MaryMaryMaryMary |
| SheilaSheilaSheilaSheila |
| SofiaSofiaSofiaSofia |
+------------------------------+
10 rows in set (0.00 sec)
Replacing Output Data
We can replace a piece of a string with another piece of text. This works similar to a str_replace type function from PHP. As an example, we’ll replace the letter a with the string BOO in all of the first names from our friends table. Note that these are not actually modifying the underlying data, but rather making the change to the data which is output. We can see this by selecting all data from the friends table and we can see that everything is still in tact and as good as new.
mysql> select replace(first_name, 'a', 'BOO') from friends; +---------------------------------+ | replace(first_name, 'a', 'BOO') | +---------------------------------+ | BrendBOO | | DBOOve | | Emmet | | FrBOOnkie | | JBOOck | | JBOOcob | | Jim | | MBOOry | | SheilBOO | | SofiBOO | +---------------------------------+ 10 rows in set (0.00 sec) mysql> select * from friends; +------------+------------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+------------+------------+------------+----------+ | Brenda | Penblanc | 719-4521 | 1970-02-15 | 5 | | Dave | Chappelle | 977-5555 | 1973-08-24 | 4 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 | | Frankie | Farmington | 978-1235 | 1977-04-17 | 6 | | Jack | Penblanc | 581-1841 | 1967-04-23 | 5 | | Jacob | Olivio | 512-3411 | 1972-10-01 | 7 | | Jim | Jones | 423-1423 | 1985-07-24 | 1 | | Mary | Olivio | 412-9823 | 1972-11-11 | 7 | | Sheila | Farmington | 591-8522 | 1977-07-12 | 6 | | Sofia | Vergara | 487-4612 | 1972-07-10 | 2 | +------------+------------+------------+------------+----------+ 10 rows in set (0.00 sec)
Reversing Data
By using the reverse() function, we can easily reverse the output of a query. Here, we’ll retrieve all first names and reverse their letters:
mysql> select reverse(first_name) from friends; +---------------------+ | reverse(first_name) | +---------------------+ | adnerB | | evaD | | temmE | | eiknarF | | kcaJ | | bocaJ | | miJ | | yraM | | aliehS | | aifoS | +---------------------+ 10 rows in set (0.00 sec)
String Concatenation with MySQL
String Concatenation is the way in which we can combine strings together. Likely you are quite familiar with this as both JavaScript and PHP use string concatenation all the time. In MySQL we can do so pretty easily as well. Let’s try a query:
mysql> select concat(first_name, ' ', last_name) from friends where last_name='Olivio'; +------------------------------------+ | concat(first_name, ' ', last_name) | +------------------------------------+ | Jacob Olivio | | Mary Olivio | +------------------------------------+ 2 rows in set (0.04 sec)
Cool! We were able to select the Olivios and have a nice formatted output by sticking together their first and last names with a space in between them.
We can take it a step further with the concat_ws() function. With this function, we can specify the separator and get as many fields as we like. Let’s test it out with a custom separator here:
mysql> select concat_ws(' @@@@@ ', first_name, last_name, cell_phone, house_id) from friends;
+-------------------------------------------------------------------+
| concat_ws(' @@@@@ ', first_name, last_name, cell_phone, house_id) |
+-------------------------------------------------------------------+
| Brenda @@@@@ Penblanc @@@@@ 719-4521 @@@@@ 5 |
| Dave @@@@@ Chappelle @@@@@ 977-5555 @@@@@ 4 |
| Emmet @@@@@ Brickowski @@@@@ 987-1244 @@@@@ 3 |
| Frankie @@@@@ Farmington @@@@@ 978-1235 @@@@@ 6 |
| Jack @@@@@ Penblanc @@@@@ 581-1841 @@@@@ 5 |
| Jacob @@@@@ Olivio @@@@@ 512-3411 @@@@@ 7 |
| Jim @@@@@ Jones @@@@@ 423-1423 @@@@@ 1 |
| Mary @@@@@ Olivio @@@@@ 412-9823 @@@@@ 7 |
| Sheila @@@@@ Farmington @@@@@ 591-8522 @@@@@ 6 |
| Sofia @@@@@ Vergara @@@@@ 487-4612 @@@@@ 2 |
+-------------------------------------------------------------------+
10 rows in set (0.00 sec)
Getting First and Last parts of a String
We can use the left() and right() functions to do just that. Each function takes the column in question as the first parameter, and the number of characters to return as the second parameter. We can try this on the address column of our houses table. We’ll grab the first 5 characters starting from the left and the first 7 characters starting from the right. Let’s check it out:
mysql> select left(address, 5), right(address, 7) from houses; +------------------+-------------------+ | left(address, 5) | right(address, 7) | +------------------+-------------------+ | 12 Du | ston MA | | 54 Ma | nway NH | | 742 A | isco CA | | 836 S | tham MA | | 87 Hi | ando FL | | 88 Bu | towe VT | | 92 Bu | nver CO | +------------------+-------------------+ 7 rows in set (0.00 sec)
Returning Length of Strings
By using the length() function we can check the length of the string in our various columns. Here we’ll check the length of all of the first and last names in our friends table. The second query is to simply display all of the names so we can confirm that yep, the length returned for all of our data is correct.
mysql> select length(first_name), length(last_name) from friends; +--------------------+-------------------+ | length(first_name) | length(last_name) | +--------------------+-------------------+ | 6 | 8 | | 4 | 9 | | 5 | 10 | | 7 | 10 | | 4 | 8 | | 5 | 6 | | 3 | 5 | | 4 | 6 | | 6 | 10 | | 5 | 7 | +--------------------+-------------------+ 10 rows in set (0.00 sec) mysql> select * from friends; +------------+------------+------------+------------+----------+ | first_name | last_name | cell_phone | birthday | house_id | +------------+------------+------------+------------+----------+ | Brenda | Penblanc | 719-4521 | 1970-02-15 | 5 | | Dave | Chappelle | 977-5555 | 1973-08-24 | 4 | | Emmet | Brickowski | 987-1244 | 2013-07-15 | 3 | | Frankie | Farmington | 978-1235 | 1977-04-17 | 6 | | Jack | Penblanc | 581-1841 | 1967-04-23 | 5 | | Jacob | Olivio | 512-3411 | 1972-10-01 | 7 | | Jim | Jones | 423-1423 | 1985-07-24 | 1 | | Mary | Olivio | 412-9823 | 1972-11-11 | 7 | | Sheila | Farmington | 591-8522 | 1977-07-12 | 6 | | Sofia | Vergara | 487-4612 | 1972-07-10 | 2 | +------------+------------+------------+------------+----------+ 10 rows in set (0.00 sec)
MySQL String Summary
As you can see, there are many ways to work with strings in MySQL. The ability to format and mold the data to our needs is very useful in MySQL. An added benefit is that unless you want to change data in your tables with insert and update statements, all of underlying data will stay in tact nicely.
