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

If you are avoiding stored procedures like the plague, or are unable to do a mysql_dump due to permissions, or running into other various reasons.

I would suggest a three-step approach like this:

1) Where this query builds a bunch of queries as a result set.

# =================
# VAR/CHAR SEARCH
# =================
# BE ADVISED USE ANY OF THESE WITH CAUTION
# DON'T RUN ON YOUR PRODUCTION SERVER 
# ** USE AN ALTERNATE BACKUP **

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' LIKE '%stuff%';') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     
        (
            A.DATA_TYPE LIKE '%text%'
        OR  
            A.DATA_TYPE LIKE '%char%'
        )
;

.

# =================
# NUMBER SEARCH
# =================
# BE ADVISED USE WITH CAUTION

SELECT 
    CONCAT('SELECT * FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE ', A.COLUMN_NAME, ' IN ('%1234567890%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE IN ('bigint','int','smallint','tinyint','decimal','double')
;

.

# =================
# BLOB SEARCH
# =================
# BE ADVISED THIS IS CAN END HORRIFICALLY IF YOU DONT KNOW WHAT YOU ARE DOING
# YOU SHOULD KNOW IF YOU HAVE FULL TEXT INDEX ON OR NOT
# MISUSE AND YOU COULD CRASH A LARGE SERVER
SELECT 
    CONCAT('SELECT CONVERT(',A.COLUMN_NAME, ' USING utf8) FROM ', A.TABLE_SCHEMA, '.', A.TABLE_NAME, 
           ' WHERE CONVERT(',A.COLUMN_NAME, ' USING utf8) IN ('%someText%');') 
FROM INFORMATION_SCHEMA.COLUMNS A
WHERE 
            A.TABLE_SCHEMA != 'mysql' 
AND     A.TABLE_SCHEMA != 'innodb' 
AND     A.TABLE_SCHEMA != 'performance_schema' 
AND     A.TABLE_SCHEMA != 'information_schema'
AND     A.DATA_TYPE LIKE '%blob%'
;

Results should look like this:

2) You can then just Right Click and use the Copy Row (tab-separated)

3) Paste results in a new query window and run to your heart’s content.

Detail: I exclude system schema’s that you may not usually see in your workbench unless you have the option Show Metadata and Internal Schemas checked.

I did this to provide a quick way to ANALYZE an entire HOST or DB if needed or to run OPTIMIZE statements to support performance improvements.

I’m sure there are different ways you may go about doing this but here’s what works for me:

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO ANALYZE THEM
SELECT CONCAT('ANALYZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

-- ========================================== DYNAMICALLY FIND TABLES AND CREATE A LIST OF QUERIES IN THE RESULTS TO OPTIMIZE THEM
SELECT CONCAT('OPTIMIZE TABLE ', TABLE_SCHEMA, '.', TABLE_NAME, ';') FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbname';

Tested On MySQL Version: 5.6.23

WARNING: DO NOT RUN THIS IF:

1. You are concerned with causing Table-locks (keep an eye on your client-connections)

2. You are unsure about what you are doing.

3. You are trying to anger you DBA. (you may have people at your desk with the quickness.)

Cheers, Jay ;-]

Время на прочтение
5 мин

Количество просмотров 61K

Описание общей потребности в поиске данных и объектов в базе данных

Поиск данных, а также хранимых процедур, таблиц и других объектов в базе данных является достаточно актуальным вопросом в том числе и для C#-разработчиков, а также и для .NET-разработки в целом.

Достаточно часто может возникнуть ситуация, при которой нужно найти:

1. объект базы данных (таблицу, представление, хранимую процедуру, функцию и т д)
2. данные (значение и в какой таблице располагается)
3. фрагмент кода в определениях объектов базы данных

Существует множество готовых решений как платных, так и бесплатных.

Сначала рассмотрим как можно осуществлять поиск данных и объектов в базе данных с помощью встроенных средств самой СУБД, а затем рассмотрим как это сделать с помощью бесплатной утилиты dbForge Search.

Поиск с помощью встроенных средств самой СУБД

Определить есть ли таблица Employee в базе данных можно с помощью следующего скрипта:

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name]='Employee';

Результат может быть примерно такой:

Здесь выводятся:

1. идентификаторы объекта и схемы, где располагается объект
2. название этой схемы и название этого объекта
3. тип объекта и описание этого типа объекта
4. даты и время создания и последней модификации объекта

Чтобы найти все вхождения строки “Project”, то можно использовать следующий скрипт:

select [object_id], [schema_id],
	   schema_name([schema_id]) as [schema_name], 
	   [name], 
	   [type], 
	   [type_desc], 
	   [create_date], 
	   [modify_date]
from sys.all_objects
where [name] like '%Project%';

Результат может быть примерно такой:

Как видно из результата, здесь подстроку “Project” содержат не только две таблицы Project и ProjectSkill, но и также некоторые первичные и внешние ключи.

Чтобы понять кому именно принадлежат данные ключи, добавим в вывод поле parent_object_id и его имя и схему, в которой он располагается следующим образом:

select ao.[object_id], ao.[schema_id],
	   schema_name(ao.[schema_id]) as [schema_name],
	   ao.parent_object_id,
	   p.[schema_id] as [parent_schema_id],
	   schema_name(p.[schema_id]) as [parent_schema_name],
	   p.[name] as [parent_name],
	   ao.[name], 
	   ao.[type], 
	   ao.[type_desc], 
	   ao.[create_date], 
	   ao.[modify_date]
from sys.all_objects as ao
left outer join sys.all_objects as p on ao.[parent_object_id]=p.[object_id]
where ao.[name] like '%Project%';

Результатом будет вывод таблицы с детальной информацией о родительских объектах, т е где определены первичные и внешние ключи:

В запросах используются следующие системные объекты:

• таблица sys.all_objects
• скалярная функция schema_name

Итак, разобрали как найти объекты в базе данных с помощью встроенных средств самой СУБД.
Теперь покажем как найти данные в базе данных на примере поиска строк.

Чтобы найти строковое значение по всем таблицам базы данных, можно воспользоваться следующим решением. Упростим данное решение и покажем как можно найти например значение “Ramiro” с помощью следующего скрипта:

set nocount on
declare @name varchar(128), @substr nvarchar(4000), @column varchar(128)
set @substr = '%Ramiro%'

declare @sql nvarchar(max);

create table #rslt 
(table_name varchar(128), field_name varchar(128), [value] nvarchar(max))

declare s cursor for select table_name as table_name from information_schema.tables where table_type = 'BASE TABLE' order by table_name
open s
fetch next from s into @name
while @@fetch_status = 0
begin
 declare c cursor for 
	select quotename(column_name) as column_name from information_schema.columns 
	  where data_type in ('text', 'ntext', 'varchar', 'char', 'nvarchar', 'char', 'sysname', 'int', 'tinyint') and table_name  = @name
 set @name = quotename(@name)
 open c
 fetch next from c into @column
 while @@fetch_status = 0
 begin
   --print 'Processing table - ' + @name + ', column - ' + @column

   set @sql='insert into #rslt select ''' + @name + ''' as Table_name, ''' + @column + ''', cast(' + @column + 
	' as nvarchar(max)) from' + @name + ' where cast(' + @column + ' as nvarchar(max)) like ''' + @substr + '''';

	print @sql;

   exec(@sql);

   fetch next from c into @column;
 end
 close c
 deallocate c
 fetch next from s into @name
end
select table_name as [Table Name], field_name as [Field Name], count(*) as [Found Mathes] from #rslt
group by table_name, field_name
order by table_name, field_name

drop table #rslt
close s
deallocate s

Результат выполнения может быть таким:

Здесь выводятся имена таблиц и в каких столбцах хранится значение, содержащие подстроку “Ramiro”. А также количество найденных входов данной подстроки для найденной пары таблица-колонка.

Чтобы найти объекты, в определениях которых есть заданный фрагмент кода, можно воспользоваться следующими системными представлениями:

1. sys.sql_modules
2. sys.all_sql_modules
3. sys.syscomments

Например, используя последнее представление, можно с помощью следующего скрипта найти все объекты, в определениях которых встречается заданный фрагмент кода:

select obj.[object_id],
	   obj.[name],
	   obj.[type_desc],
	   sc.[text]
from sys.syscomments as sc
inner join sys.objects obj on sc.[id]=obj.[object_id]
where sc.[text] like '%code snippet%';

Здесь будет выведен идентификатор, название, описание и полное определение объекта.

Поиск с помощью бесплатной утилиты dbForge Search

Однако, более удобно поиск производить с помощью готовых хороших инструментов. Одним из таких инструментов является dbForge Search.

Для вызова этой утилиты в окне SSMS нажмите на кнопку .

Появится следующее окно поиска:

Обратите внимание на верхнюю панель (слева направо):

1. можно переключать режим поиска (ищем DDL (объекты) или данные)
2. непосредственно что ищем (какую подстроку)
3. учитывать ли регистр, искать точное соответствие слову, искать вхождения:

   

4. группировать результат по типам объектов — кнопка
5. выбрать нужные типы объектов для поиска:

   

6. также можно задать несколько баз данных для поиска и выбрать экземпляр MS SQL Server

Это все в режиме поиска объектов, т е когда включен DDL:

В режиме поиска данных изменится только выбор типов объектов:

А именно будут доступны для выбора только таблицы, где и хранятся собственно сами данные:

Теперь как и раньше найдем все вхождения подстроки “Project” в названиях объектов:

Как видно, был выбран режим поиска по DDL-объектам, заполнено что ищем-строка “Project”, остальное все было по умолчанию.

При выделении найденного объекта внизу отображается код определения данного объекта или всего его родительского объекта.

Также можно переместить навигацию на найденный объект, щелкнув на кнопку :

Можно также сгруппировать найденные объекты по их типу:

Обратите внимание, что выводятся даже те таблицы, в которых есть поля, в именах которых содержится подстрока “Project”. Однако, напомним, что режим поиска можно менять: искать полное соответствие/частичное/учитывать регистр или нет.

Теперь найдем значение “Ramiro” по всем таблицам:

Обратите внимание, что внизу отображаются все строки, в которых содержится подстрока “Ramiro” выбранной таблицы Employee.

Также можно переместить навигацию к найденному объекту, нажав как и ранее на кнопку :

Таким образом мы можем искать нужные объекты и данные в базе данных.

Заключение

Были рассмотрены способы поиска как самих данных, так и объектов в базе данных как с помощью встроенных средств самой СУБД MS SQL Server, так и с помощью бесплатной утилиты dbForge Search.

Также от компании Devart есть и ряд других бесплатных готовых решений, полный список которых можно посмотреть здесь.

Источники

• Search_Script.sql
• SSMS
• dbForge Search
• Документация по Microsoft SQL
• Бесплатные решения от компании Devart

In this article, we will explore SUBSTRING, PATINDEX and CHARINDEX string functions for SQL queries.

Introduction

While working with the string data, we perform various calculations, analytics, search, replace strings using SQL
queries. SQL Server provides many useful functions such as ASCII, CHAR, CHARINDEX, CONCAT, CONCAT_WS, REPLACE,
STRING_AGG, UNICODE, UPPER for this purpose. In this article, we explore SUBSTRING, PATINDEX, and CHARINDEX using examples.

The SUBSTRING() function extracts the substring from the specified string based on the specified location.

Syntax for SUBSTRING() function:

SUBSTRING(expression, starting_position, length)

• Expression: In this argument, we specify a character, binary, text, ntext, or image expression
• starting_position: It contains an integer or bigint expression. It defines the starting position
  from where we extract the substring. The first character in the string starts with the value 1
• Length: It is a positive integer value that defines how many characters from the string, from the
  starting_position, we want to retrieve

In the below example, we retrieve a substring using the specified inputs.

We can understand the substring output using the following image.

If we change the starting position and length parameter, it returns the modified substring.

SUBSTRING function with an expression

In the below SQL query, we use the LEN() function to calculate the [lastname] length in the starting_position argument.

Here, it gets the starting position dynamically depending upon the length of a person’s first name.

CHARINDEX function in SQL queries

The CHARINDEX() function returns the substring position inside the specified string. It works reverse to the SUBSTRING
function. The substring() returns the string from the starting position however the CHARINDEX returns the substring
position.

Syntax of CHARINDEX() function:

CHARINDEX(substring, input_string)

• Substring: Here, we define the substring that we want to search in the input string. We can specify a maximum of 8000 characters in this argument
• Input_String: In this argument, we define the input string

In the below example, we retrieve the position of substring SQLSHACK.COM using the CHARINDEX. It returns the starting
position of the substring as 16. In the earlier example of the SUBSTRING function, we specified the starting position
16 to returns the SQLSHACK.COM string.

CHARINDEX function can also perform case-sensitive searches. We need to use the COLLATE() function for a
case-sensitive search.

For example, in the below query, we use the COLLATE function along with the collation latin_general_cs_as.

It returns zero in the output because it considers sqlshack.com separate from SQLSHACK.COM.

Let’s change the substring in the capital letter to match with the string.

It returns the position of SUBSTRING using the case-sensitive search using the CHARINDEX function.

We can also add an optional starting position in the CHARINDEX() function. For example, in the below query, the 2^nd query specifies a starting position at 8. Therefore, it starts looking for the substring from the
8^th character position.

PATINDEX function in SQL queries

The PATINDEX() function looks for the first occurrence of a pattern in the input string and returns the starting
position of it.

Syntax of PATINDEX() function:

PATINDEX ( ‘%Pattern%’,input_string )

• %Pattern%: In this argument, we specify the character expression that we want to look into the
  specified string. We might include the wild-characters as well in this argument
• input_string: It is the string in which we want to search the pattern

In the below example, we search the pattern %author% in the specified string.

You can use the wildcard character % and _ to find the positions of the pattern as well. In this example, we search
the position for the pattern SQ followed by Shack in the string. It is similar to a LIKE operator.

In the below SQL query, we use the [^] string operator. It finds out the position of the character without an
alphabet, number or space.

In the below example, we use the PATINDEX() function for a table column. It checks for the pattern – frame in the
[Name] column of the [Production].[Product] table.

Use of SUBSTRING and CHARINDEX functions together in SQL queries

In many cases, we combine these functions to produce the required result. For example, Suppose your table holds mail addresses for your customer. You want to fetch the domain names ( such as gmail.com, outlook.com) from the email addresses.

In the below query, we combine the CHARINDEX and SUBSTRING function.

You can understand the output of the query using the below image.

• The CHARINDEX function returns the position of character @ from the [emailaddress] column
• The LEN() function is for calculating the length of the email address
• The SUBSTRING function returns the substring from the [emailaddress] as per the position returned by the CHARINDEX

Similarly, suppose you have a table that employee joining date, time and day. You want to extract the joining date in
another column. Similar to the other example, you can use the CHARINDEX, and SUBSTRING() function in the below SQL
query.

In the output, we get the dates from the [Messages] column strings.

Conclusion

In this article, we explored the SUBSTRING, PATINDEX, and CHARINDEX string functions for SQL queries. You can retrieve a specific text, data using a combination of these functions. As a beginner, you can first write these functions individually and later combine the functions.