Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
MySQL provides a wide variety of string functions for use in your queries. I'll cover the major ones here. In this lesson, we'll be using the world database. We'll start with a simple query that get's the length of the LocalName from the Country table. And so we're going to SELECT and we'll get both Name and LocalName. But we're just going to take the length of the LocalName, using the LENGTH function. And we're selecting just for WHERE Continent equals Europe, so we have a manageable list of countries.
And we'll order it by that length and you'll notice that I gave the LENGTH an alias, using the AS clause. I'm using that AS clause for the ORDER BY. So, we don't have to use the whole LENGTH function twice in the query. We'll order descending, so that the larger ones are at the top. And I'll press Go. And here we have our lengths. And so you can see the LocalName here, and the Length of the local name. And here's something interesting to notice.
Let's get down to some of the smaller ones. You see the ones with accented characters. This one here has six letters, but it shows the Length as 7. And that discrepancy is because the accented letter actually takes up two bytes in the Unicode character set. This is UTF8. And so some of the accent letters take up 2 bytes rather than just 1, and so the length of Espania even though it's six letters shows up as 7, because the length function is actually counting the bytes rather than the characters.
So there's another version of the length function called CHAR_LENGTH, CHAR_LENGTH. And if we use that instead, you notice that our result is different. We get down here to some of the smaller ones again. Here you can see Espania, which was 7 before is now 6. And Romania shows as 7 instead of 8. And that's because now we're counting the characters rather than the bytes. Of course the character length function will take a little bit longer because it has to parse out the Unicode and compare it to the table and all.
Whereas the LENGTH function can just count bytes. But if you're really concerned with length and character then character length is the function that you'll want to use. TheY're also string functions for subsets, and take out this ORDER BY because we're not going to be using the LENGTH anymore, and now we'll SELECT Name, and some subsets of name we'll say LEFT. And that will get the left three characters from the Name column. And we can do the same thing with RIGHT.
And there's one called MID for getting substrings from the middle of a string. And this takes two parameters, so we'll say Name comma 2 comma 3. And that will get the three characters starting at the second character position. And, so when I say Go here, you see that, here we have the left three letters, the right three letters. So starting from the right, nia, and the middle three, starting at the second column. So the lba, or in this case, ndo.
So it's very simple, these are simple ways to get substrings from strings using LEFT, RIGHT and MID. There's also concatenation functions, we can concatenate, Name and LocalName. And this'll simply take those two columns and jam them together like this. So if you wanted to put something in between them using concatenate, you would have to, or we can say comma space, and you would concatenate that literal string along with it, and that would work fine.
Or you can say concatenate with separator, WS. And we can take that separator and put it as the first parameter. And that way, we could also concatenate some other things if we wanted to say, Region and Continent. And those will all be concatenated with the separator, comma, space, or we could use a slash like this. And so you can concatenate with or without the separators.
CONCAT_WS is the version with separators. There's also a LOCATE function. And start this just to show you how it works without a query from a table. We can say LOCATE bar in foobarbaz. And it will give us the character position so, one, two, three, four. It should return a 4. And there's our 4. So it says that the string bar begins at the fourth position within that other string foobarbaz.
And of course you can use this in a query from a table. So you can say, LOCATE stan, and we'll give it a name here first, so we can see the whole name. From Name column FROM Country WHERE Continent equals Europe. Or rather, WHERE Name LIKE. And it has the string, stan, in it. And so there's our countries with stan in them. You notice it's towards the end of all of them. But we have that.
Starts at the eighth position in Afghanistan the sixth position in Kazakhstan or we can simply locate a single letter if we want to. Okay, I'll put my K in the LIKE as well. And these are all the countries with K's in them, we got 26 rows. So the LOCATE function is great for locating a string within another string. There's also simple functions for uppercase and lowercase, and they also work for Unicode. And you'll notice that this lowercase e with the accent over it, the double dots, the umlaut, comes up in uppercase here.
And there's another lowercase e like that, and some of these other ones here. There's the slashed o. And likewise, there's one for lowercase, so I can type LOWER here and LOWER here. And it does the same thing, except it makes them lowercase. And you'll notice this capital O and Oosterlicht is made into a lowercase o. And finally there's a function for reversing character strings. And that also works with Unicode. Now you see our strings are reversed.
So MySQL provides a rich set of string function. These are just the most commonly used. You can see the MySQL documentation for the rest of them.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 98382 Viewers
61 Video lessons · 85669 Viewers
71 Video lessons · 69603 Viewers
56 Video lessons · 101928 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.