Sql как найти символ в столбцах - Исправление недочетов и поиск решений вместе с Examum.ru

Or, you can use my query here, should be simpler then having to create sProcs for each DB you want to search: FullParam SQL Blog

/* Reto Egeter, fullparam.wordpress.com */

DECLARE @SearchStrTableName nvarchar(255), @SearchStrColumnName nvarchar(255), @SearchStrColumnValue nvarchar(255), @SearchStrInXML bit, @FullRowResult bit, @FullRowResultRows int
SET @SearchStrColumnValue = '%searchthis%' /* use LIKE syntax */
SET @FullRowResult = 1
SET @FullRowResultRows = 3
SET @SearchStrTableName = NULL /* NULL for all tables, uses LIKE syntax */
SET @SearchStrColumnName = NULL /* NULL for all columns, uses LIKE syntax */
SET @SearchStrInXML = 0 /* Searching XML data may be slow */

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
CREATE TABLE #Results (TableName nvarchar(128), ColumnName nvarchar(128), ColumnValue nvarchar(max),ColumnType nvarchar(20))

SET NOCOUNT ON

DECLARE @TableName nvarchar(256) = '',@ColumnName nvarchar(128),@ColumnType nvarchar(20), @QuotedSearchStrColumnValue nvarchar(110), @QuotedSearchStrColumnName nvarchar(110)
SET @QuotedSearchStrColumnValue = QUOTENAME(@SearchStrColumnValue,'''')
DECLARE @ColumnNameTable TABLE (COLUMN_NAME nvarchar(128),DATA_TYPE nvarchar(20))

WHILE @TableName IS NOT NULL
BEGIN
    SET @TableName = 
    (
        SELECT MIN(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME))
        FROM    INFORMATION_SCHEMA.TABLES
        WHERE       TABLE_TYPE = 'BASE TABLE'
            AND TABLE_NAME LIKE COALESCE(@SearchStrTableName,TABLE_NAME)
            AND QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) > @TableName
            AND OBJECTPROPERTY(OBJECT_ID(QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME)), 'IsMSShipped') = 0
    )
    IF @TableName IS NOT NULL
    BEGIN
        DECLARE @sql VARCHAR(MAX)
        SET @sql = 'SELECT QUOTENAME(COLUMN_NAME),DATA_TYPE
                FROM    INFORMATION_SCHEMA.COLUMNS
                WHERE       TABLE_SCHEMA    = PARSENAME(''' + @TableName + ''', 2)
                AND TABLE_NAME  = PARSENAME(''' + @TableName + ''', 1)
                AND DATA_TYPE IN (' + CASE WHEN ISNUMERIC(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@SearchStrColumnValue,'%',''),'_',''),'[',''),']',''),'-','')) = 1 THEN '''tinyint'',''int'',''smallint'',''bigint'',''numeric'',''decimal'',''smallmoney'',''money'',' ELSE '' END + '''char'',''varchar'',''nchar'',''nvarchar'',''timestamp'',''uniqueidentifier''' + CASE @SearchStrInXML WHEN 1 THEN ',''xml''' ELSE '' END + ')
                AND COLUMN_NAME LIKE COALESCE(' + CASE WHEN @SearchStrColumnName IS NULL THEN 'NULL' ELSE '''' + @SearchStrColumnName + '''' END  + ',COLUMN_NAME)'
        INSERT INTO @ColumnNameTable
        EXEC (@sql)
        WHILE EXISTS (SELECT TOP 1 COLUMN_NAME FROM @ColumnNameTable)
        BEGIN
            PRINT @ColumnName
            SELECT TOP 1 @ColumnName = COLUMN_NAME,@ColumnType = DATA_TYPE FROM @ColumnNameTable
            SET @sql = 'SELECT ''' + @TableName + ''',''' + @ColumnName + ''',' + CASE @ColumnType WHEN 'xml' THEN 'LEFT(CAST(' + @ColumnName + ' AS nvarchar(MAX)), 4096),''' 
            WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + '),'''
            ELSE 'LEFT(' + @ColumnName + ', 4096),''' END + @ColumnType + ''' 
                    FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
            INSERT INTO #Results
            EXEC(@sql)
            IF @@ROWCOUNT > 0 IF @FullRowResult = 1 
            BEGIN
                SET @sql = 'SELECT TOP ' + CAST(@FullRowResultRows AS VARCHAR(3)) + ' ''' + @TableName + ''' AS [TableFound],''' + @ColumnName + ''' AS [ColumnFound],''FullRow>'' AS [FullRow>],*' +
                    ' FROM ' + @TableName + ' (NOLOCK) ' +
                    ' WHERE ' + CASE @ColumnType WHEN 'xml' THEN 'CAST(' + @ColumnName + ' AS nvarchar(MAX))' 
                    WHEN 'timestamp' THEN 'master.dbo.fn_varbintohexstr('+ @ColumnName + ')'
                    ELSE @ColumnName END + ' LIKE ' + @QuotedSearchStrColumnValue
                EXEC(@sql)
            END
            DELETE FROM @ColumnNameTable WHERE COLUMN_NAME = @ColumnName
        END 
    END
END
SET NOCOUNT OFF

SELECT TableName, ColumnName, ColumnValue, ColumnType, COUNT(*) AS Count FROM #Results
GROUP BY TableName, ColumnName, ColumnValue, ColumnType

Источник

При работе с базой данных 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».

Источник

В этом учебном пособии вы узнаете, как использовать в SQL Server условие LIKE (Transact-SQL) для выполнения сопоставления с шаблоном с синтаксисом и примерами.

Описание

Условие LIKE SQL Server (Transact-SQL) определяет, совпадает ли указанная символьная строка с заданным шаблоном в предложении WHERE в операторе SELECT, INSERT, UPDATE или DELETE. Это позволяет выполнять сопоставление образцов.

Синтаксис

Синтаксис условия LIKE в SQL Server (Transact-SQL):

expression LIKE pattern [ ESCAPE ‘escape_character’ ]

Параметры или аргументы

expression — символьное выражение, такое как столбец или поле.
pattern — символьное выражение, содержащее подстановочный символ. Подстановочные символы, которые вы можете выбрать:

символ	пояснение
%	Позволяет вам сопоставлять любую строку любой длины (включая нулевую длину)
_	Позволяет вам сопоставлять один символ
[ ]	Позволяет вам сопоставлять любой символ в скобках [] (например, [abc] будет соответствовать символам a, b или c)
[^]	Позволяет вам сопоставлять любой символ, не находящийся в скобках [^] (например, [^ abc] будет соответствовать любому символу, который не является символом a, b или c)

escape_character — необязательный. Это позволяет вам проверять для буквенных экземпляров символа подстановки, например % или _.

Пример использования подстановочного символа % (символ процента)

Первый пример SQL Server LIKE, который мы рассмотрим, включает использование шаблона %.

Давайте объясним, как шаблон % работает в SQL Server условии LIKE. Мы хотим найти всех employees (сотрудников), чье имя last_name начинается с ‘Б’.
Например:

SELECT *

FROM employees

WHERE last_name LIKE ‘Б%’;

Этот пример условия SQL Server LIKE вернет всех employees у которых last_name будет начинаться на ‘Б’ такие как ‘Брошкин’, ‘Баранников’, ‘Богомолец’ и т.д.

Вы также можете использовать групповой символ % несколько раз в одной строке. Например:

SELECT *

FROM employees

WHERE last_name LIKE ‘%o%’;

В этом примере SQL LIKE мы ищем всех employees (сотрудников), у которых last_name содержит букву ‘o’.

Пример использования подстановочного символа _ (символ подчеркивания)

Затем давайте объясним, как подстановочный символ _ (символ подчеркивания) работает в условии SQL Server LIKE. Помните, что _ ищет только один символ.
Например:

SELECT *

FROM employees

WHERE last_name LIKE ‘Кр_т’;

Этот пример условия SQL LIKE возвращает всех employees (сотрудников), чье имя last_name равно 4 символам, причем первые два символа — «Кр», а последний символ — «т». Например, он может вернуть сотрудников, чье last_name является «Крот», «Крат», «Крут», «Крит» и т.д.

Вот еще один пример:

SELECT *

FROM employees

WHERE employee_number LIKE ‘123_’;

Вам может понадобиться найти номер employee_number, но у вас есть только 3 из 4 цифр. В приведенном выше примере будет извлечено потенциально 10 записей (где отсутствующее значение могло бы равняться чему угодно от 0 до 9). Например, он может вернуть employees, чьи employee_number:

1230, 1231, 1232, 1233, 1234, 1235, 1236, 1237, 1238, 1239

Пример использования подстановочного символа [] (квадратные скобки).

Далее, давайте объясним, как подстановочный символ [] (квадратные скобки) работает в условии SQL Server LIKE. Помните, что то, что содержится в квадратных скобках, это символы, которые вы пытаетесь сопоставить.
Например:

SELECT *

FROM employees

WHERE first_name LIKE ‘К[ио]тов’;

Этот пример условия LIKE SQL Server возвращает всех employees, чье имя first_name равно 5 символам, причем первый символ — «К», а три последних символа — «тов», а второй символ — «и» или «о». Таким образом, в этом случае он будет соответствовать либо «Китов», либо «Котов».

Пример использования подстановочного символа [^] (квадратные скобки с символом ^).

Затем давайте объясним, как подстановочный символ [^] (квадратные скобки с подстановочным символом ^) работает в условии SQL Server LIKE. Помните, что то, что содержится в квадратных скобках, это символы, которые вы НЕ хотите сопоставлять.
Например:

SELECT *

FROM employees

WHERE first_name LIKE ‘К[^ио]тов’;

Этот пример условия SQL Server LIKE возвращает всех сотрудников, чье имя first_name равно 5 символам, причем первый символ — «К», а три последних символа — «тов», а второй символ не является «и» или «о». Таким образом, в этом случае он будет соответствовать таким значениям, как «Катов», «Кутов», «Кетов» и т.д. Но это не будет соответствовать ни «Китов», ни «Котов».

Пример использования оператора NOT

Затем давайте посмотрим, как использовать SQL Server NOT Operator с помощью подстановочных символов.

Будем использовать % с оператором NOT. Вы также можете использовать условие SQL LIKE для поиска employees (сотрудников), имя которых не начинается с буквы «Б».
Например:

SELECT *

FROM employees

WHERE last_name NOT LIKE ‘Б%’;

Поместив оператор NOT перед условием LIKE SQL Server, вы сможете получить всех employees, имя которых не начинается с «Б».

Пример использования символов Escape

Важно понимать, как сопоставить «Escape Characters» с pattern. Эти примеры относятся конкретно к экранирующим символам в SQL Server.

Предположим, вы хотели найти символ % или _ в условии SQL Server LIKE. Вы можете сделать это, используя символ Escape.

Обратите внимание, что вы можете определить escape-символ только как один символ (length = 1).
Например:

SELECT *

FROM employees

WHERE secret_hint LIKE ‘123!%455’ ESCAPE ‘!’;

Этот пример условия SQL LIKE идентифицирует символ ! как escape-символ. Это предложение вернет всех employees, чей secret_hint составляет 123%455.

Вот еще один более сложный пример использования escape-символов в SQL Server условии LIKE.

SELECT *

FROM employees

WHERE secret_hint LIKE ‘H%!%’ ESCAPE ‘!’;

Этот пример условия SQL LIKE возвращает всех employees, чей secret_hint начинается с ‘H’ и заканчивается на ‘%’. Например, он возвращает значение, например «Help%».

Вы также можете использовать escape-символ с символом _ в условии SQL Server LIKE.
Например:

SELECT *

FROM employees

WHERE secret_hint LIKE ‘H%!_’ ESCAPE ‘!’;

Этот пример условия SQL LIKE возвращает всех employees, чей secret_hint начинается с ‘H’ и заканчивается на ‘_’. Например, он вернет значение, например «Help_».

Источник

Процедура для поиска значений во всех столбцах таблицы.

Проверено на Postgres.

Смысл таков: выбираем все столбцы таблицы из схемы. Потом обходим выбранные значения в ‘цикле’ и собираем запрос, который выбирает количество совпадений с искомым значением больше нуля.

CREATE PROCEDURE `findValue`(
    IN `_value` VARCHAR(50),
    IN `_table` VARCHAR(50),
    IN `_database` VARCHAR(50)
)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Search the value in any columns of table.'
BEGIN

DECLARE done BOOL;
DECLARE t, c varchar(250);
DECLARE _query LONGTEXT DEFAULT '';
DECLARE cur CURSOR FOR SELECT table_name, column_name FROM information_schema.columns WHERE table_schema = _database AND table_name = _table;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = true;

Open cur;
read_loop: LOOP
    FETCH cur INTO t, c;

    IF done THEN
        LEAVE read_loop;
    END IF;

        IF _query <> '' THEN
            SET _query = concat(_query, ' UNION ');
        END IF;

        SET _query = concat(_query, 'SELECT "', t, '" as tbl, "', c, '" as col, count(', c, ') as have from ', concat(_database, '.', t), ' WHERE ', c, ' like "%', _value, '%" HAVING have > 0');


END LOOP;
Close cur; 

SET @q = _query;
prepare qqq from @q;
execute qqq;
END

вызов: call findValue(‘value’, ‘table_namer’, ‘database’);

Источник

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)

Содержание

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

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

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

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

Заключение

Описание

Синтаксис

Параметры или аргументы

Пример использования подстановочного символа % (символ процента)

Пример использования подстановочного символа _ (символ подчеркивания)

Пример использования подстановочного символа [] (квадратные скобки).

Пример использования подстановочного символа [^] (квадратные скобки с символом ^).

Пример использования оператора NOT

Пример использования символов Escape

Table of Contents

RIGHT and LEFT

CHARINDEX

SUBSTRING

Using them together

References

See Also