From the course: MySQL Essential Training

String comparisons - MySQL Tutorial

From the course: MySQL Essential Training

Start my 1-month free trial Buy for my team

String comparisons

“

- [Bill] Hi, I'm Bill Wyman. MySQL has facilities for comparing strings in several manners. For this lesson, we'll use the world database. So I'll use world and execute that, that opens the world database. For example, if I wanted all the countries with a letter A in the second position of the country name, I could use a like clause like this, SELECT Name FROM country WHERE Name LIKE, and then I give it a matching string and ORDER BY Name. And when I execute this, I get all of the country names with an A in the second position. So you notice this LIKE clause and that takes a wild card string. And the wild card string uses the underscore as a single character wild card and the percent symbol as a multi character wild card. So this is saying everything like a string with anything in the first position, an A in the second position followed by anything else, and that gets me this result. There's also a string compare function. And I'm just going to paste this down here and say WHERE, and instead of all of this, we'll say STRCMP, that's string compare, S-T-R-C-M-P Name, France <= 0 Now, what happens when we execute this, and I'll go ahead and execute it and you'll see, we get all of the strings that are less than or equal to France. And so if I come down here to the end, you see France is the last one, and we have everything that sorts before that as well. So the STRCMP function, the string compare function returns -1 if the left hand string sorts lower than the right, it returns 0 if they're equal and it returns +1 if the left hand string sorts higher than the right. So in this case, I'm getting all of the countries with a name that sorts lower than France. So these are a few of the more common ways to compare strings in MySQL.

Contents