The last thing I want to cover in this mini-series on string matching is how to make our search a little more fuzzy. Let me show you an issue with our current implementation. If we search for the term Tim O'Reilly, we don't get any records. However, if we search for the term Tim O'Reilly with an apostrophe, then we do get a record back. This isn't great. It means our users must type in the name exactly as it is, or the search will not find a match. And, of course, there's other examples.

as well as from the search columns. Let me show you how. In our search scope, we'll start by updating our search terms. We'll use a preg_replace function to automatically remove all characters from our $searchTerm that are not an uppercase or lowercase letter or a number. Now we need to update the columns in the database to also be normalized, otherwise we won't get a match. And we can do this by running a regular expression right in our database query.

And we can do this by running a regular expression right in our database query. Let's start with the first name. Instead of using a where, we're going to use a whereRaw so that we can run an expression. And then we'll use a regular expression function to replace any non-alphanumeric characters that might exist in this column. And then we'll check the results of that against our searchTerm using the like comparison.

And then we'll check the results of that against our searchTerm using the like comparison. Notice here that even though we're using a raw expression, we're still passing through our searchTerm as a binded parameter, and we do that to avoid any SQL injection issues. Okay, let's update our other two columns. We'll just copy this, change this to an orWhereRaw, change this to lastName, and then if we go down to the companyName, we'll do the same thing here.

change this to last_name, and then if we go down to the company_name, we'll do the same thing here, changing this to companies.name. Okay, that's it. Let's test this out. Now if we run the Tim O'Reilly search again with the apostrophe, you can see that we're still able to find the result, and if we remove the apostrophe, we're still able to find that record. Nice.

Almost any time you're running an operation inline in your query like this, the database is not going to be able to use an index. However, what you can do is create an index for that particular expression. Let's try that. Let's go to our company migration, and then let's add a new virtual column for our regular expression. table, string, nameNormalized, virtual as,

This is just a good practice.

from the name column since we don't need it anymore. This is just a good practice. Indexes are not free. They require additional disk space, and they require additional computation every time a column changes. So unless you absolutely need the index, remove it. Okay, let's make the same change in our User's migration. We'll just copy this as a starting point. Let's go down and create two new virtual columns.

We'll just copy this as a starting point. Let's go down and create two new virtual columns. Let's update the column names, and then let's also remove the existing indexes on the first and last name columns, just like we did in our company's migration. Okay, that's it. Let's rerun our migrations in our seeder. php artisan migrate:fresh with the database seeder. Finally, let's update our search scope.

php artisan migrate:fresh with the database seeder. Finally, let's update our search scope to use these new virtual columns. We no longer need the whereRaw method here. We can just use a regular where statement, where firstNameNormalized is like our search term, or where lastNameNormalized is like our search term. And finally, we'll update our companyName where companies.nameNormalized is like our search term. All right, that's it.

where companies.nameNormalized is like our search term. All right, that's it. Let's go to the browser and give it a try. Remember, our Tim O'Reilly search is now running at a total combined query time of roughly 595 milliseconds. And if we hit refresh, we're back down to less than seven milliseconds. And again, if we add an apostrophe to the search, it still works. Using a regular expression to normalize your search terms.