Sql как найти по части текста

При работе с базой данных SQL вам может понадобиться найти записи, содержащие определенные строки. В этой статье мы разберем, как искать строки и подстроки в MySQL и SQL Server.

Содержание

• Использование операторов WHERE и LIKE для поиска подстроки
• Поиск подстроки в SQL Server с помощью функции CHARINDEX
• Поиск подстроки в SQL Server с помощью функции PATINDEX
• MySQL-запрос для поиска подстроки с применением функции SUBSTRING_INDEX()

Я буду использовать таблицу products_data в базе данных products_schema. Выполнение команды SELECT * FROM products_data покажет мне все записи в таблице:

Поскольку я также буду показывать поиск подстроки в SQL Server, у меня есть таблица products_data в базе данных products:

Поиск подстроки при помощи операторов WHERE и LIKE

Оператор WHERE позволяет получить только те записи, которые удовлетворяют определенному условию. А оператор LIKE позволяет найти определенный шаблон в столбце. Эти два оператора можно комбинировать для поиска строки или подстроки.

Например, объединив WHERE с LIKE, я смог получить все товары, в которых есть слово «computer»:

SELECT * FROM products_data
WHERE product_name LIKE '%computer%'

Знаки процента слева и справа от «computer» указывают искать слово «computer» в конце, середине или начале строки.

Если поставить знак процента в начале подстроки, по которой вы ищете, это будет указанием найти такую подстроку, стоящую в конце строки. Например, выполнив следующий запрос, я получил все продукты, которые заканчиваются на «er»:

SELECT * FROM products_data
WHERE product_name LIKE '%er'

А если написать знак процента после искомой подстроки, это будет означать, что нужно найти такую подстроку, стоящую в начале строки. Например, я смог получить продукт, начинающийся на «lap», выполнив следующий запрос:

SELECT * FROM products_data
WHERE product_name LIKE 'lap%'

Этот метод также отлично работает в SQL Server:

Поиск подстроки в SQL Server с помощью функции CHARINDEX

CHARINDEX() — это функция SQL Server для поиска индекса подстроки в строке.

Функция CHARINDEX() принимает 3 аргумента: подстроку, строку и стартовую позицию для поиска. Синтаксис выглядит следующим образом:

CHARINDEX(substring, string, start_position)

Если функция находит совпадение, она возвращает индекс, по которому найдено совпадение, а если совпадение не найдено, возвращает 0. В отличие от многих других языков, отсчет в SQL начинается с единицы.

Пример:

SELECT CHARINDEX('free', 'free is the watchword of freeCodeCamp') position;

Как видите, слово «free» было найдено на позиции 1. Это потому, что на позиции 1 стоит его первая буква — «f»:

Можно задать поиск с конкретной позиции. Например, если указать в качестве позиции 25, SQL Server найдет совпадение, начиная с текста «freeCodeCamp»:

SELECT CHARINDEX('free', 'free is the watchword of freeCodeCamp', 25);

При помощи CHARINDEX можно найти все продукты, в которых есть слово «computer», выполнив этот запрос:

SELECT * FROM products_data WHERE CHARINDEX('computer', product_name, 0) > 0

Этот запрос диктует следующее: «Начиная с индекса 0 и до тех пор, пока их больше 0, ищи все продукты, названия которых содержат слово «computer», в столбце product_name». Вот результат:

Поиск подстроки в SQL Server с помощью функции PATINDEX

PATINDEX означает «pattern index», т. е. «индекс шаблона». Эта функция позволяет искать подстроку с помощью регулярных выражений.

PATINDEX принимает два аргумента: шаблон и строку. Синтаксис выглядит следующим образом:

PATINDEX(pattern, string)

Если PATINDEX находит совпадение, он возвращает позицию этого совпадения. Если совпадение не найдено, возвращается 0. Вот пример:

SELECT PATINDEX('%ava%', 'JavaScript is a Jack of all trades');

Чтобы применить PATINDEX к таблице, я выполнил следующий запрос:

SELECT product_name, PATINDEX('%ann%', product_name) position
FROM products_data

Но он только перечислил все товары и вернул индекс, под которым нашел совпадение:

Как видите, подстрока «ann» нашлась под индексом 3 продукта Scanner. Но скорее всего вы захотите, чтобы выводился только тот товар, в котором было найдено совпадение с шаблоном.

Чтобы обеспечить такое поведение, можно использовать операторы WHERE и LIKE:

SELECT product_name, PATINDEX('%ann%', product_name) position
FROM products_data
WHERE product_name LIKE '%ann%'

Теперь запрос возвращает то, что нужно.

MySQL-запрос для поиска строки с применением функции SUBSTRING_INDEX()

Помимо решений, которые я уже показал, MySQL имеет встроенную функцию SUBSTRING_INDEX(), с помощью которой можно найти часть строки.

Функция SUBSTRING_INDEX() принимает 3 обязательных аргумента: строку, разделитель и число. Числом обозначается количество вхождений разделителя.

Если вы укажете обязательные аргументы, функция SUBSTRING_INDEX() вернет подстроку до n-го разделителя, где n — указанное число вхождений разделителя. Вот пример:

SELECT SUBSTRING_INDEX("Learn on freeCodeCamp with me", "with", 1);

В этом запросе «Learn on freeCodeCamp with me» — это строка, «with» — разделитель, а 1 — количество вхождений разделителя. В этом случае запрос выдаст вам «Learn on freeCodeCamp»:

Количество вхождений разделителя может быть как положительным, так и отрицательным. Если это отрицательное число, то вы получите часть строки после указанного числа разделителей. Вот пример:

SELECT SUBSTRING_INDEX("Learn on freeCodeCamp with me", "with", -1);

От редакции Techrocks: также предлагаем почитать «Индексы и оптимизация MySQL-запросов».

Заключение

Из этой статьи вы узнали, как найти подстроку в строке в SQL, используя MySQL и SQL Server.

CHARINDEX() и PATINDEX() — это функции, с помощью которых можно найти подстроку в строке в SQL Server. Функция PATINDEX() является более мощной, так как позволяет использовать регулярные выражения.

Поскольку в MySQL нет CHARINDEX() и PATINDEX(), в первом примере мы рассмотрели, как найти подстроку в строке с помощью операторов WHERE и LIKE.

Перевод статьи «SQL Where Contains String – Substring Query Example».

MySQL Full Text searching appears to be great and the best way to search in SQL. However, I seem to be stuck on the fact that it won’t search partial words. For instance if I have an article titled «MySQL Tutorial» and search for «MySQL», it won’t find it.

Having done some searching I found various references to support for this coming in MySQL 4 (i’m using 5.1.40). I’ve tried using «MySQL» and «%MySQL%», but neither works (one link I found suggested it was stars but you could only do it at the end or the beginning not both).

Here’s my table structure and my query, if someone could tell me where i’m going wrong that would be great. I’m assuming partial word matching is built in somehow.

CREATE TABLE IF NOT EXISTS `articles` (
  `article_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `article_name` varchar(64) NOT NULL,
  `article_desc` text NOT NULL,
  `article_link` varchar(128) NOT NULL,
  `article_hits` int(11) NOT NULL,
  `article_user_hits` int(7) unsigned NOT NULL DEFAULT '0',
  `article_guest_hits` int(10) unsigned NOT NULL DEFAULT '0',
  `article_rating` decimal(4,2) NOT NULL DEFAULT '0.00',
  `article_site_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `article_time_added` int(10) unsigned NOT NULL,
  `article_discussion_id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `article_source_type` varchar(12) NOT NULL,
  `article_source_value` varchar(12) NOT NULL,
  PRIMARY KEY (`article_id`),
  FULLTEXT KEY `article_name` (`article_name`,`article_desc`,`article_link`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=7 ;

INSERT INTO `articles` VALUES
(1, 'MySQL Tutorial', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 6, 3, 1, '1.50', 1, 1269702050, 1, '0', '0'),
(2, 'How To Use MySQL Well', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 1, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(3, 'Optimizing MySQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(4, '1001 MySQL Tricks', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 1, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(5, 'MySQL vs. YourSQL', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0'),
(6, 'MySQL Security', 'Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. It has survived not only five centuries, but also the leap into electronic typesetting, remaining essentially unchanged. It was popularised in the 1960s with the release of Letraset sheets containing Lorem Ipsum passages, and more recently with desktop publishing software like Aldus PageMaker including versions of Lorem Ipsum.', 'http://www.domain.com/', 0, 2, 0, '3.00', 1, 1269702050, 1, '0', '0');

SELECT count(a.article_id) FROM articles a

            WHERE MATCH (a.article_name, a.article_desc, a.article_link) AGAINST ('mysql')
            GROUP BY a.article_id
            ORDER BY a.article_time_added ASC

The prefix is used as it comes from a function that sometimes adds additional joins.

As you can see a search for MySQL should return a count of 6, but unfortunately it doesn’t.

Update

No results where returned as every single row was matched.

http://dev.mysql.com/doc/refman/5.1/en/fulltext-natural-language.html

«The search result is empty because the word “MySQL” is present in at least 50% of the rows. As such, it is effectively treated as a stopword. For large data sets, this is the most desirable behavior: A natural language query should not return every second row from a 1GB table. For small data sets, it may be less desirable.»

SQL Pattern Matching :

It is used for searching a string or a sub-string to find certain character or group of characters from a string. We can use LIKE Operator of SQL to search sub-string. The LIKE operator is used with the WHERE Clause to search a pattern in string of column. The LIKE operator is used in a conjunction with the two wildcards characters.

1. Percentage sign( % ) : It represents zero, one or multiple characters of variable length.
2.  Underscore ( _ ) : It represents one, single character of fixed length.

Example :

In this example we will create a schema for our database and named it as geeksforgeeks. After that we will create a table inside it with the name geeks_data and tried to search a sub string from the data of table.

Step 1: Create a database :

In order to create a database we need to use the CREATE operator.

CREATE DATABASE geeksforgeeks;

Step 2: Create a table inside the database :

In this step we will create the table geeks_data inside the geeksforgeeks database.

CREATE TABLE geeksforgeeks.geeks_data(id INT, 
                                    first_name VARCHAR(255),
                                    last_name VARCHAR(255),
                                    text_description  VARCHAR(255),
                                    PRIMARY KEY(id));

Step 3: Insert data into the table :

In order to insert the data inside the database we need to use INSERT operator.

INSERT INTO geeksforgeeks.geeks_data (id, first_name, last_name, text_description) 
VALUES (1, "Rahul", "Khanna", "I am a backend developer who also like to technical content writing");

id	first_name	last_name	text_description
1	Rahul	Khanna	I am a backend developer who also like to technical content writing

Step 4: Searching the pattern using Like operator :

SELECT first_name FROM geeksforgeeks.geeks_data WHERE text_description LIKE '%backend%developer%';

Step 5: Output :

We will get the first_name where in the description backend developer is present.

first_name

Rahul

Last Updated :
10 Sep, 2021

Like Article

Save Article

---

This article explains the functionality and uses of the LEFT, RIGHT, SUBSTRING and CHARINDEX functions in SQL.

This article will leave you with sound knowledge and understanding that you can take away and questions will be asked no more.

We»ll start by explaining each function individually with examples; and then I will show a scenario where these functions can be used together.

---

RIGHT and LEFT

These functions might sound fairly self explanatory, but unless you have a fundamental understanding of how they behave; you will experience some problems that will keep you scratching your head for a while.

As you can see by this illustration, the LEFT function starts BEFORE the left-most character of a string and moves to the right, while the RIGHT function starts AFTER the right-most character and moves inwards to the left.

Here’s the result:

As you can see, the RIGHT function has expectedly taken the last three characters of the string passed into it, and the LEFT function has taken the first three. Pretty simple!

---

CHARINDEX

CHARINDEX is another simple function that accepts two arguments. The first argument is the character you are searching for; the second is the string. It will return the first index position that the character passed into the first argument is within the
string.

Now let’s use our CHARINDEX function to find the position of the space in this string:

Here’s the result:

As you can see, the position of the space within «Hello World» is the 6th character. CHARINDEX can be a useful function for finding occurrences of a character within a table programmatically. I will build on this subject later on in this article.

---

SUBSTRING

I would consider SUBSTRING to be the most useful of all the functions mentioned today. It accepts three arguments, the string, a start position and how many characters it will «step over». Let’s take a look at that illustration from earlier:

Now I’ll write a simple query to show the use of SUBSTRING:

And now the results:

As you can see. SUBSTRING includes spaces as a position within a string. So executing this query shows a «window» of the string that has been passed to it. If we had executed the query as «SELECT SUBSTRING(‘HELLO WORLD’,6,5)» then the results would have
shown » WORL».

---

Using them together

Now I’m going to show an example of how to use these together. Imagine you have a table with a column called «Name», within that column you have various names, with different lengths; but all have one thing in common, a space. You’re asked to only display
the forename, but because there are differing lengths you will need to find the occurring space in the string.

We can use the CHARINDEX function to find the position of the space within the row programmatically. In this instance, the position is «7». Now we’ve found that we just need to display everything left of that position. We can «wrap» this up within a LEFT
statement to do this (simple right?!).

Notice how I’ve put a «-1» after the CHARINDEX function? This is because the CHARINDEX function is finding the space in the string, but we don’t really want to include this in our resultset, so we’re basically saying «find the position of the space minus
one». A good way to see this is by looking at the illustration from earlier and count the positions that the function will step over. Remember that the LEFT function takes two arguments, so we need to state the expression within that as well. This will of
course return the result «JOHNNY».

We hope this helps, thanks for reading and if you have any further questions then don’t hesitate to comment below.

This entry participates in the TechNet Guru contributions for June, 2013 contest.

---

References

• String Functions (Transact-SQL)
• CHARINDEX (Transact-SQL)

---

See Also

• T-SQL: Split String with a Twist
• Transact-SQL Portal

---