Like and Soundex - Database search and finding by the sound of words

Like and Soundex Operators - Database Searching and Sound-Based Word Searches

The most commonly used command in databases is a query command, the SELECT command, which, as the name implies, selects or displays data from one or more tables in a database. It should always be used with some clause like Where or limit, which allows only data with conditions to be displayed. This command executes most of the searches in the Database; for example, when we perform a period query with a start and end, we have the SELECT command, the WHERE clause, and the BETWEEN operator in action. But what about when we need to perform searches within texts, how can we use the SELECT command, the WHERE clause, and the LIKE, NOT LIKE, SOUNDEX operators to carry out these searches? And what are phonetic searches? See this article for answers to these questions and amazing tips that will help improve your queries not only in MySQL but in any relational database.

TL;DR;

The Like and Not Like operators are used for database searching, and use the characters % _ ‘ where % represents any character, the underscore represents a position, and the apostrophe denotes the start and end of the expression. ’_jalm%' this example would capture the name Djalma, meaning it starts with any character, must have jalm, and ends with any character.
Full-Text Search is a search in long texts in text-type fields with indexes that facilitate locating the searched term.
Soundex - phonetic search, each word has a pronunciation code, called a phonetic code, which consists of the first letter of the word and a numeric sequence. Words with similar pronunciations like michael and mikael have the same phonetic code. Soundex performs the search this way.

The Like and Not Like Operator

The Like operator means 'like this', and is used to perform searches in the database. This operator uses three characters to determine the type of search:

' Apostrophe start and end of expression
% Any character, a wildcard character that can also represent a set of characters
_ Exactly one position, _ _ represent two positions

Start with:

SELECT * FROM clientes where nomeCli like ‘João%’

In this case, all clients whose name starts with João and ends with anything will be displayed.

End with:

SELECT * FROM clientes where nomeCli like ‘%Rangel'

In this case, all clients whose name ends with Rangel and starts with anything will be displayed.

Exactly:

SELECT * FROM clientes where nomeCli like ‘João Henrique Rangel’

In this case, all clients whose name is exactly João Henrique Rangel will be displayed, similar to an equality search.

Anywhere:

SELECT * FROM clientes where nomeCli like ‘%Henrique%’

In this case, all clients whose name contains Henrique anywhere, at the beginning, middle, or end, will be displayed.

We always use the Like operator to make a search that matches the specified expression, while the Not Like operator is just the opposite of the expression, meaning all but this:

Anywhere:

 SELECT * FROM clientes where nomeCli not like ‘%Henrique%’

In this case, all clients whose name does not contain Henrique anywhere, at the beginning, middle, or end, will be displayed.

Full-Text Search

Full-Text Search is a mechanism and a type of index in MySQL servers that allows users to perform Full-Text queries on tables in MySQL. To use it, a full-text index must be created on the table before executing the queries. The full-text index can include one or more text-type columns from the table.

FULLTEXT is a full-text type index in MySQL.
InnoDB or MyISAM type tables can use Full-text indexes.
Full-text indexes can only be created on columns of types VARCHAR, CHAR, or TEXT.
A FULLTEXT index can be created with the CREATE TABLE commands or added later using ALTER TABLE or CREATE INDEX.

CREATE TABLE users(
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(200),
description TEXT,
FULLTEXT(title,description)
) ENGINE=InnoDB;

We insert some records, and then we perform the query:

SELECT * FROM users WHERE MATCH(title,description) AGAINST('curso mysql 8' IN NATURAL LANGUAGE MODE)

In this case, it will use the indexes to locate more quickly as with the AGAINST or MATCH.

Soundex

Search by the sound of words, this is the impressive feature of Soundex. Have you ever not known exactly how to spell a word, especially in English? This operator can help a lot. The phonetic search converts each string into a numeric sequence to be compared.

select soundex('Michael');
select soundex('Mychael');
select soundex('Mykhael');
select soundex('Myckhael');

The result:

M240

CREATE TABLE student
(
   student_id INT AUTO_INCREMENT,
   student_name VARCHAR(100) NOT NULL,
   student_class VARCHAR(20) NOT NULL,
   PRIMARY KEY(student_id )

);

INSERT INTO student
(student_name, student_class )
VALUES
   ('Michael Jordan', 'IX'),
   ('Michael Jackson', 'X' ),
   ('Michael Corleone', 'XI' ),
   ('Michael Caio', 'X'  ),
   ('Mychael Jordan', 'IX' ),
   ('Mykhael Jackson', 'X' ),
   ('Myckhael Corleone', 'X'  ),
   ('Micael Caio', 'XII');

Now a search with names that start with Michael.

select * from student where student_name like '%Michael%';

Now see the magic of soundex, let's isolate the first name with the substring_index function separating where it finds a space ‘ ’

select * from student
where soundex(substring_index(student_name , ' ', 1)) = soundex('Michael');

MySQL 8 Course

To explain the use of the Like, Not Like, and Soundex operators, along with many other topics, we recorded and launched the MySQL 8 course. Take advantage of an exclusive discount coupon for those reading this article; just visit the following link and the discount will be applied. Purchase today

https://www.udemy.com/course/curso-mysql-8/?referralCode=8FD45C56E26A755DB63A

So that's it; I hope this brief article sheds some light on how searches are carried out in databases.

See you in the next video in some Hcode course. Until then!

mysql-8