MySQL Has Functions (Part 2) : String Theory

Welcome to Part 2 of my introduction to MySQL's built in functions. In this series I'll be looking at some of the useful functions MySQL provides you with, and how you can use them to make your PHP (or other language) website's code cleaner and more efficient.

MySQL Has Functions (Part 1) has covered MySQL's date and time functions. In this part I'll be going over some of the string manipulation functions MySQL makes available to you. To find out more, you can check out the MySQL String function manual page.

MySQL Has Functions (Part 2)


I recommend that after reading this article, fire up phpMyAdmin (or whatever other DB admin tool you use) and try some of them out. It may give you some great ideas on how you can make use of these functions in your future works. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.

UPPER() & LOWER()

Here are a couple of simple functions to start with. You can probably work out what they do from their names.

UPPER() makes a string uppercase, and LOWER() makes it lowercase. They're quite simple to use, just pass either a string, or a table field containing a string, as a parameter like so:

"SELECT UPPER('Joe Bloggs')"

And you'll be returned the uppercase version 'JOE BLOGGS'. The same goes for lowercasing a string with LOWER().

So in your PHP code, if you ever use the functions strtolower() or strtoupper() on a field returned from your SQL, you might find it easier to integrate that into your query, for example:

"SELECT LOWER(user_name) AS user_name_lower FROM users WHERE user_id = 11"

SUBSTRING()

Another obviously named function is SUBSTRING(). It allows you to extract a specific portion of a string.

It takes 3 parameters: the string you wish the function to work on, the starting point of the extraction, and the length of the portion you wish to extract.

On a number of websites - including ones that I've worked on - I've seen preview snippets of content generated in the following way:

$sql = "SELECT news_content FROM news WHERE news_id = 23";
$article = mysql_query($sql);
$articleRow = mysql_fetch_assoc($article);
$preview = substr($articleRow['news_content'], 0, 75);

But I find something like this to be a more elegant solution, even if it doesn't reduce the number of lines of code.

$sql = "SELECT news_content, SUBSTRING(news_content, 1, 75) AS news_preview FROM news WHERE news_id = 23";
$article = mysql_query($sql);
$articleRow = mysql_fetch_assoc($article);
$preview = $articleRow['news_preview'];

If you're used to using PHP's substr() function then you might notice something a little odd – in MySQL the position of the first character of a string is 1, not 0, as it is in PHP.

You don't have to specify the 3rd parameter; if you leave it out SUBSTRING(), it will extract every character from the starting point all the way to the end of the string.

REPLACE()

To be honest, REPLACE() is a function that I've never actually used in a query on a website I've made.

What I have found it useful for is the odd bit of maintenance on database tables where an unwanted string or misspelling has crept into multiple records, and it would be too time-consuming to do manual corrections.

So, to do a 'find and replace' on one of your tables you would write a query something like this.

"UPDATE table_name SET table_field = REPLACE(table_field, 'string to find', 'string to replace it with')"

But ALWAYS make a backup of your tables BEFORE running a query like this.

CONCAT()

The CONCAT() function can be easily used to join one or more strings together. Each string is passed as a parameter to the function, and you can pass as many as you like.

If, for example, one of our tables stored the title, forename, and surname of a person, and we wanted to display them as one string, we could write our code like this:

$sql = "SELECT CONCAT(person_title, ' ', person_fname, ' ', person_sname) AS name FROM people";
$people = mysql_query($sql);
while($person = mysql_fetch_assoc($people)) {
	echo $person['name'] . '<br />';
}

Rather than using good old-fashioned, and in my opinion, slightly messy PHP string concatenations like this:

$sql = "SELECT person_title, person_fname, person_sname FROM people";
$people = mysql_query($sql);
while($person = mysql_fetch_assoc($people)) {
	echo $person['person_title'] . ' ' . $person['person_fname'] . ' ' . $person['person_sname'] . '<br />';
}

The more observant among you will notice that in both cases we had to add a single space character in between each field/variable in order to make the output look nice.

Thankfully the guys at MySQL have provided us with a way around this problem – a variation on CONCAT() called CONCAT_WS().

The WS stands for 'with separator' and it works something like PHP's implode() function. The first parameter passed will be used like a sort of glue to join all the other strings together, like so:

$sql = "SELECT CONCAT_WS(' ', person_title, person_fname, person_sname) AS name FROM people";
$people = mysql_query($sql);
while($person = mysql_fetch_assoc($people)) {
	echo $person['name'] . '<br />';
}

The end result is our desired output in one easy-to-use variable, coming from a nice tidy SQL statement.

LIKE()

The LIKE() function is used for matching strings. While LIKE() is similar in function to the = sign, it has one great advantage when you want to match more than an exact value – wildcards.

The two wildcard characters allowed are % which will match zero or more of any character, and _ which will match exactly one of any character.

So let's say for example that you're writing a simple search query for a CMS application, and you want to find any articles where the user's query appears in the title. You would probably write something like this:

'SELECT * FROM articles WHERE article_title LIKE "%My Search Phrase%"'

And the rows returned will be ones where the article's title at some point contains 'My Search Phrase'.

RLIKE()

RLIKE() is similar to LIKE() except that you can use regular expressions to perform advanced pattern matching, like so:

'SELECT * FROM articles WHERE article_title RLIKE ".*My Search Phrase.*"'

This SQL query will have exactly the same result as the LIKE() example, but because RLIKE() gives you access to the full power of regular expressions, you can do a lot more.

Check out the MySQL regular expressions manual page for more information and examples on how you can use them in your queries. I particularly recommend you look at the [[:<:]] and [[:>:]] markers. These mark word boundaries and can make it very easy to improve the quality of search queries by limiting the search to whole words.

To Wrap It Up

MySQL's string functions are a little harder to go into much practical detail on because when and how you can make use of them will very much depend on the specific coding problem you're faced with.

However, I hope with this series, you'll be a little more aware of them and can make use of them to make your development life easier. Be sure to Subscribe to our RSS Feed or Follow us on Twitter to receive updates for the rest of the series.