Как найти подстроку в строке 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».

Источник

RIGHT and LEFT
CHARINDEX
SUBSTRING
Using them together
References
See Also

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.

SELECT RIGHT('HELLO WORLD', 3);

SELECT LEFT('HELLO WORLD', 3);

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:

SELECT CHARINDEX(' ','Hello World');

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:

SELECT SUBSTRING('HELLO WORLD',4,5)

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.

SELECT CHARINDEX(' ','JOHNNY BELL')

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?!).

SELECT LEFT('HELLO WORLD',CHARINDEX(' ','HELLO WORLD')-1)

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)

CHARINDEX (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

This function searches for one character expression inside a second character expression, returning the starting position of the first expression if found.

:::image type=»icon» source=»../../includes/media/topic-link-icon.svg» border=»false»::: Transact-SQL syntax conventions

Syntax

CHARINDEX ( expressionToFind , expressionToSearch [ , start_location ] )

[!INCLUDEsql-server-tsql-previous-offline-documentation]

Arguments

expressionToFind
A character expression containing the sequence to find. expressionToFind has an 8000 character limit.

expressionToSearch
A character expression to search.

start_location
An integer or bigint expression at which the search starts. If start_location is not specified, has a negative value, or has a zero (0) value, the search starts at the beginning of expressionToSearch.

Return types

bigint if expressionToSearch has an nvarchar(max), varbinary(max), or varchar(max) data type; int otherwise.

Remarks

If either the expressionToFind or expressionToSearch expression has a Unicode data type (nchar or nvarchar), and the other expression does not, the CHARINDEX function converts that other expression to a Unicode data type. CHARINDEX cannot be used with image, ntext, or text data types.

If either the expressionToFind or expressionToSearch expression has a NULL value, CHARINDEX returns NULL.

If CHARINDEX does not find expressionToFind within expressionToSearch, CHARINDEX returns 0.

CHARINDEX performs comparisons based on the input collation. To perform a comparison in a specified collation, use COLLATE to apply an explicit collation to the input.

The starting position returned is 1-based, not 0-based.

0x0000 (char(0)) is an undefined character in Windows collations and cannot be included in CHARINDEX.

Supplementary Characters (Surrogate Pairs)

When using SC collations, both start_location and the return value count surrogate pairs as one character, not two. For more information, see Collation and Unicode Support.

Examples

A. Returning the starting position of an expression

This example searches for bicycle in the searched string value variable @document.

DECLARE @document VARCHAR(64);  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bicycle', @document);  
GO

[!INCLUDEssResult]

B. Searching from a specific position

This example uses the optional start_location parameter to start the search for vital at the fifth character of the searched string value variable @document.

DECLARE @document VARCHAR(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('vital', @document, 5);  
GO

[!INCLUDEssResult]

-----------   
16            
  
(1 row(s) affected)

C. Searching for a nonexistent expression

This example shows the result set when CHARINDEX does not find expressionToFind within expressionToSearch.

DECLARE @document VARCHAR(64);  
  
SELECT @document = 'Reflectors are vital safety' +  
                   ' components of your bicycle.';  
SELECT CHARINDEX('bike', @document);  
GO

[!INCLUDEssResult]

-----------
0
  
(1 row(s) affected)

D. Performing a case-sensitive search

This example shows a case-sensitive search for the string 'TEST' in searched string 'This is a Test``'.

USE tempdb;  
GO  
--perform a case sensitive search  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);

[!INCLUDEssResult]

This example shows a case-sensitive search for the string 'Test' in 'This is a Test'.

  
USE tempdb;  
GO  
SELECT CHARINDEX ( 'Test',  
       'This is a Test'  
       COLLATE Latin1_General_CS_AS);

[!INCLUDEssResult]

E. Performing a case-insensitive search

This example shows a case-insensitive search for the string 'TEST' in 'This is a Test'.

USE tempdb;  
GO  
SELECT CHARINDEX ( 'TEST',  
       'This is a Test'  
       COLLATE Latin1_General_CI_AS);  
GO

[!INCLUDEssResult]

Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]

F. Searching from the start of a string expression

This example returns the first location of the string is in string This is a string, starting from position 1 (the first character) of This is a string.

SELECT CHARINDEX('is', 'This is a string');

[!INCLUDEssResult]

G. Searching from a position other than the first position

This example returns the first location of the string is in string This is a string, starting the search from position 4 (the fourth character).

SELECT CHARINDEX('is', 'This is a string', 4);

[!INCLUDEssResult]

H. Results when the string is not found

This example shows the return value when CHARINDEX does not find string string_pattern in the searched string.

SELECT TOP(1) CHARINDEX('at', 'This is a string') FROM dbo.DimCustomer;

[!INCLUDEssResult]

What We’ll Cover

How to Query for Strings in SQL with the WHERE Clause and LIKE Operator
How to Query for Strings in SQL Server with the CHARINDEX Function
How to Query for Strings in SQL Server with the PATINDEX Function
How to Query for Strings in MySQL with the SUBSTRING_INDEX() Function
Conclusion

How to Query for Strings in SQL with the `WHERE` Clause and `LIKE` Operator

The WHERE clause lets you get only the records that meet a particular condition. The LIKE operator, on the other hand, lets you find a particular pattern in a column. You can combine these two to search for a string or a substring of a string.

I was able to get all the products that have the word “computer” in them by combining the WHERE clause and LIKE operator by running the query below:

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

The percentage sign before and after the word “computer” means, find the word “computer” whether it’s in the end, middle, or start.

So, if you put the percentage sign at the start of a substring you’re searching by, it means, find that substring at the end of a string. For Example, I got every product that ends with “er” by running this query:

SELECT * FROM products_data
WHERE product_name LIKE '%er'

And if it’s at the end of a string, it means, find that substring at the start of a string. For example, I was able to get the product that starts with “lap” with this query:

SELECT * FROM products_data
WHERE product_name LIKE 'lap%'

This method also works fine in SQL Server:

How to Query for Strings in SQL Server with the `CHARINDEX` Function

CHARINDEX() is an SQL server function for finding the index of a substring in a string.

The CHARINDEX() function takes 3 arguments – the substring, the string, and the starting position. The syntax looks like this:

CHARINDEX(substring, string, start_position)

If it finds a match, it returns the index where it finds the match, but if it doesn’t find a match, it returns 0. Unlike many other languages, counting in SQL is 1-based.

Here’s an example:

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

You can see the word free was found in position 1. That’s because ‘f’ itself is at position 1:

If I specify 25 as the position, SQL Server would find a match starting from the “freeCodeCamp” text:

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

I was able to use the CHARINDEX function to search for all products that have the word “computer” in them by running this query:

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

That query is saying, start from index 0, as long as they’re more than 0, get me every product that has the word “computer” in them in the product_name column. This is the result:

How to Query for Strings in SQL Server with the `PATINDEX` Function

PATINDEX stands for “pattern index”. So, with this function, you can search for a substring with regular expressions.

PATINDEX takes two arguments – the pattern and the string. The syntax looks like this:

PATINDEX(pattern, string)

If PATINDEX finds a match, it returns the position of that match. If it doesn’t find a match, it returns 0. Here’s an example:

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

To apply PATINDEX to the example table, I ran this query:

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

But it only listed every product and returned the index where it found the match:

You can see it found the word “ann” at index 3 of the product Scanner. On many occasions, you might not want this behavior because you would want it to show only the item matched.

I made it return only what gets matched by using the WHERE clause and LIKE operator:

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

Now it’s behaving as you would want.

How to Query for Strings in MySQL with the `SUBSTRING_INDEX()` Function

Apart from the solutions I’ve already shown you, MySQL has an inbuilt SUBSTRING_INDEX() function with which you can find a part of a string.

The SUBSTRING_INDEX() function takes 3 compulsory arguments – the string, the substring to search for, and a delimiter. The delimiter has to be a number.

When you specify the compulsory arguments, the SUBSTRING_INDEX() function will get you every part of the string that occurs before the delimiter you specify. Here’s an example:

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

In the query above, «Learn on freeCodeCamp with me» is the string, «with» is the substring and 1 is the delimiter. In this case, the query will get you “Learn on freeCodeCamp”:

The delimiter can also be a negative number. If it’s a negative number, it gets you each part of the string that occurs after the delimiter you specify. Here’s an example:

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

Conclusion

This article showed you how to locate a substring in a string in SQL using both MySQL and SQL Server.

CHARINDEX() and PATINDEX() are the functions with which you can search for a substring in a string inside SQL Server. PATINDEX() is more powerful because it lets you use regular expressions.

Since CHARINDEX() and PATINDEX() don’t exist in MySQL, the first example showed you how you can find a substring in a string with the WHERE clause and LIKE operator.

Thank you for reading!

Learn to code for free. freeCodeCamp’s open source curriculum has helped more than 40,000 people get jobs as developers. Get started

Источник

Quick Example: Check if the String contains a Substring

Two simple SQL query examples to check if the String contains a Substring:

How to check if the string contains a substring in SQL?

In SQL Server, there are 3 main ways to check if a string contains a substring:

1. Use the LIKE operator

The LIKE operator is used in SQL to match a string with a pattern.

We can use the LIKE operator to check if the string contains a substring in several ways:

Character	Description	Example
a%	Matches any substring starting with character `a`	SELECT * FROM Customers WHERE CustomerName LIKE ‘a%’
%a	Matches any substring ending with character `a`	SELECT * FROM Customers WHERE CustomerName LIKE ‘%a’
%joe%	Matches any substring containing the string `joe`	SELECT * FROM Customers WHERE CustomerName LIKE ‘%joe%’
_	Matches any single character	SELECT * FROM Customers WHERE CustomerName LIKE ‘_oe’

Case-insensitive substring search

If you are dealing with a case-sensitive database, make sure to do the substring comparisons against the normalized string, for example:

The SQL query above performs a case-insensitive check if a string CustomerName contains a substring joe.

2. CHARINDEX — Find the position index of a substring

In SQL Server, the CHARINDEX function returns the index position of a substring within a string. If the returned value is greater than 0, it means our string contains a substring:

SQL Query to check if string contains a substring in SQL Server

Keep in mind: In C, C++, and C#, the first element in a string has index position 0. However, in SQL, the first character in a string is at position 1. So when using functions like CHARINDEX to find the position of a substring within a string, a return value of 0 indicates that the substring was not found, not that it was found at the first position in the string.

Case-insensitive substring search

3. PATINDEX — Find the position index of a substring pattern

PATINDEX is another function that helps you find a substring within a string. PATINDEX uses regular expressions to search for the substring, while CHARINDEX uses simple string matching:

PATINDEX is more flexible than CHARINDEX since it uses regular expressions for more complex pattern matching. However, it is not as fast as CHARINDEX since regular expressions require more processing power.

How to find the location of a substring within a string?

In SQL Server, you can use the CHARINDEX function to find the location of a substring within a string:

The table below explains the parameters of the CHARINDEX function:

Parameter	Description
substring	The substring that you want to find the location of
string	The string that you want to search in
start_location	An optional parameter that indicates the position in the string where the search should start. The default value is 1, meaning the search starts at the beginning of the string.

SQL Query to check substring position index in SQL Server

Case-sensitive substring match

Most of the SQL database management systems, including SQL Server, are case-insensitive by default, meaning that the LIKE operator will match substrings regardless of their case.

However, it’s also possible to configure a database to be case-sensitive, in which case the LIKE operator would only match substrings that have the same case as the search pattern.

How to check if SQL Server is case sensitive?

To check if a SQL Server database is case-sensitive, you can use the following method:

Connect to your SQL Server instance using SQL Server Management Studio (SSMS) or another tool.
Run the following query to check the collation of the database:

Replace ‘database_name’ with the name of your database.

The query will return the collation of the database, for example, ‘SQL_Latin1_General_CP1_CI_AS’.
If the collation ends with ‘CI_AS’, it means the database is case-insensitive. If it ends with ‘_BIN’ or ‘CS_AS’, it means the database is case-sensitive.
If you want to check the collation of all the databases in the SQL Server instance, you can use the following query:

SQL Query to get database case sensitivity

Published on: Jan 23, 2023

Источник

Содержание

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

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

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

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

Заключение

Table of Contents

RIGHT and LEFT

CHARINDEX

SUBSTRING

Using them together

References

See Also

CHARINDEX (Transact-SQL)

Syntax

Arguments

Return types

Remarks

Supplementary Characters (Surrogate Pairs)

Examples

A. Returning the starting position of an expression

B. Searching from a specific position

C. Searching for a nonexistent expression

D. Performing a case-sensitive search

E. Performing a case-insensitive search

Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]

F. Searching from the start of a string expression

G. Searching from a position other than the first position

H. Results when the string is not found

See also

What We’ll Cover

How to Query for Strings in SQL with the WHERE Clause and LIKE Operator

How to Query for Strings in SQL Server with the CHARINDEX Function

How to Query for Strings in SQL Server with the PATINDEX Function

How to Query for Strings in MySQL with the SUBSTRING_INDEX() Function

Conclusion

Quick Example: Check if the String contains a Substring

How to check if the string contains a substring in SQL?

1. Use the LIKE operator

Case-insensitive substring search

2. CHARINDEX — Find the position index of a substring

Case-insensitive substring search

3. PATINDEX — Find the position index of a substring pattern

How to find the location of a substring within a string?

Case-sensitive substring match

How to check if SQL Server is case sensitive?

How to Query for Strings in SQL with the `WHERE` Clause and `LIKE` Operator

How to Query for Strings in SQL Server with the `CHARINDEX` Function

How to Query for Strings in SQL Server with the `PATINDEX` Function

How to Query for Strings in MySQL with the `SUBSTRING_INDEX()` Function