From the course: MySQL Essential Training (2019)

String comparisons - MySQL Tutorial

From the course: MySQL Essential Training (2019)

Start my 1-month free trial Buy for my team

String comparisons

“

- [Instructor] MySQL has facilities for comparing strings in several manners. For this lesson, we'll use the world database. So I'll type USE world and execute. For example, if I wanted all the countries with the 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 _a%, and ORDER BY name. And when I execute this, I'll get a list of all the countries in the country database, the name columns in the country database, where there's an A in the second position of the name. It's not the first letter, it's the second letter of the name. And they're in order by name, they're in alphabetical order by name. So the LIKE string uses the underscore as a single character wildcard and the percent sign as a multicharacter wildcard. And so what this means, you can read this as any character followed by a letter A, followed by zero or more of any character. Now there's a str compare, STRCMP function that compares two strings by sort value, and it returns minus one if the left-hand string sorts lower than the right, zero if they're equal, and plus one if the left-hand string sorts higher than the right. So let's take a look an example of that. I can say SELECT name FROM country WHERE, and I can use STRCMP and for the first parameter I'll type name, and for the second parameter I'll type the literal string France. And I'll say less than or equal to zero so this is for the names that sort lower than the name France, and ORDER BY name. So now we'll get a list of the countries with a name that sorts lower than France. And I'll execute, and we'll notice, we scroll down here, and we see we get almost to France, but we include France 'cause we said less than or equal to, so we get all the way to France, and no further. So these are some of the more common ways to compare strings in MySQL.

Contents