Как найти строчку в 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 ;-]

If you’re working with a database, whether large or small, there might be occasions when you need to search for some entries containing strings.

In this article, I’ll show you how to locate strings and substrings in MySQL and SQL Server.

I‘ll be using a table I call products_data in a products_schema database. Running SELECT * FROM products_data shows me all the entries in the table:

Since I’ll be showing you how to search for a string in SQL Server too, I have the products_data table in a products database:

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

There are lots of workable answers already. Just thought I would add one I came up with that has a lot of optional funcionality.

--=======================================================================
--  MSSQL Unified Search
--  Minimum compatibility level = 130 (SQL Server 2016)
--      NOTE: The minimum compatibility level is required by the built-in STRING_SPLIT() function.
--          However, you can create the STRING_SPLIT() function at the bottom of this script for
--          lower versions of MSSQL Server.
--
--  Usage:
--      Set the parameters below and execute this script.
--
/************************ Enter Parameters Here ************************/
/**/
/**/    DECLARE @SearchString VARCHAR(1000) = 'string to search for';  -- Accepts SQL wilcards
/**/
/**/    DECLARE @IncludeUserTables BIT = 1;
/**/    DECLARE @IncludeViews BIT = 0;
/**/    DECLARE @IncludeStoredProcedures BIT = 0;
/**/    DECLARE @IncludeFunctions BIT = 0;
/**/    DECLARE @IncludeTriggers BIT = 0;
/**/
/**/    DECLARE @DebugMode BIT = 0;
/**/    DECLARE @ExcludeColumnTypes NVARCHAR(500) = 'text, ntext, char, nchar, timestamp, bigint, tinyint, smallint, bit, date, time, smalldatetime, datetime, datetime2, real, money, float, decimal, binary, varbinary, image';  -- Comma delimited list
/**/
/***********************************************************************/


SET NOCOUNT ON;
SET @SearchString = QUOTENAME(@SearchString,'''');

DECLARE @Results TABLE ([ObjectType] NVARCHAR(200), [ObjectName] NVARCHAR(200), [ColumnName] NVARCHAR(400), [Value] NVARCHAR(MAX), [SelectStatement] NVARCHAR(1000));
DECLARE @ExcludeColTypes TABLE (system_type_id INT);

INSERT INTO @ExcludeColTypes ([system_type_id])
    SELECT [system_type_id]
    FROM sys.types WHERE
    [name] IN (
        SELECT LTRIM(RTRIM([value])) FROM STRING_SPLIT(@ExcludeColumnTypes,',')
        );

DECLARE @ObjectType NVARCHAR(200);
DECLARE @ObjectName NVARCHAR(200);
DECLARE @Value NVARCHAR(MAX);
DECLARE @SelectStatement NVARCHAR(1000);
DECLARE @Query NVARCHAR(4000);


/********************* Table Objects *********************/
IF (@IncludeUserTables = 1)
BEGIN
    DECLARE @TableObjectId INT = (SELECT MIN([object_id]) FROM sys.tables);
    DECLARE @ColumnId INT;
    WHILE @TableObjectId IS NOT NULL
    BEGIN
    
        SELECT @ObjectType = 'USER TABLE';
        SELECT @ObjectName = '[' + SCHEMA_NAME([schema_id]) + '].[' + OBJECT_NAME(@TableObjectId) + ']' FROM sys.tables WHERE [object_id] = @TableObjectId;

        SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId);
        WHILE @ColumnId IS NOT NULL
        BEGIN

            SELECT @Value = '[' + [name] +']' FROM sys.columns WHERE [object_id] = @TableObjectId AND column_id = @ColumnId;

            SET @SelectStatement = 'SELECT * FROM ' + @ObjectName + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

            SET @Query = 'SELECT '
                + QUOTENAME(@ObjectType, '''')
                + ', ' + QUOTENAME(@ObjectName, '''')
                + ', ' + QUOTENAME(@Value, '''')
                + ', ' + @Value
                + ', ''' + REPLACE(@SelectStatement,'''','''''') + ''''
                + ' FROM ' + @ObjectName
                + ' WHERE CAST(' + @Value + ' AS NVARCHAR(4000)) LIKE ' + @SearchString + ';';

            IF @DebugMode = 0
            BEGIN
                INSERT INTO @Results EXEC(@Query);
            END;
            ELSE
            BEGIN
                PRINT 'Select Statement:  ' + @SelectStatement;
                PRINT 'Query:  ' + @Query;
            END;

            SET @ColumnId = (SELECT MIN([column_id]) FROM sys.columns WHERE [system_type_id] NOT IN (SELECT [system_type_id] FROM @ExcludeColTypes) AND [object_id] = @TableObjectId AND [column_id] > @ColumnId);
        END;

        SET @TableObjectId = (SELECT MIN([object_id]) FROM sys.tables WHERE [object_id] > @TableObjectId);
    END;
END;

/********************* Objects Other than Tables *********************/
SET @Query = 'SELECT ' +
    'ObjectType = CASE ' +
        'WHEN b.[type] = ''V'' THEN ''VIEW'' ' +
        'WHEN b.[type] = ''P'' THEN ''STORED PROCEDURE'' ' +
        'WHEN b.[type] = ''FN'' THEN ''SCALAR-VALUED FUNCTION'' ' +
        'WHEN b.[type] = ''IF'' THEN ''TABLE-VALUED FUNCTION'' ' +
        'WHEN b.[type] = ''TR'' THEN ''TRIGGER'' ' +
    'END ' +
    ',[ObjectName] = ''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'' ' +
    ',[ColumnName] = NULL ' +
    ',[Value] = a.[definition] ' +
    ',[SelectStatement] = ''SP_HELPTEXT '' + QUOTENAME(''['' + SCHEMA_NAME(b.[schema_id]) + ''].['' + OBJECT_NAME(a.[object_id]) + '']'','''''''') + '';'' ' +
'FROM [sys].[sql_modules] a ' +
'JOIN [sys].[objects] b ON a.[object_id] = b.[object_id] ' +
'WHERE ' +
    '( ' +
    '   a.[definition] LIKE ' + @SearchString + 
    ') ' +
    'AND ' +
    '( ' +
    '   ( ' +
            CAST(@IncludeViews AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''V'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeStoredProcedures AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''P'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeFunctions AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''FN'',''IF'') ' +
    '   ) ' +
    '   OR ' +
    '   ( ' +
            CAST(@IncludeTriggers AS VARCHAR(1)) + ' = 1 ' +
    '       AND ' +
    '       b.[type] IN (''TR'') ' +
    '   ) ' +
    '); ';

IF @DebugMode = 0
BEGIN
    INSERT INTO @Results EXEC(@Query);
END;
ELSE
BEGIN
    PRINT 'Select Statement:  ' + @SelectStatement;
    PRINT 'Query:  ' + @Query;
END;

IF @DebugMode = 0
BEGIN
    SELECT 
        [ObjectType]
        ,[ObjectName]
        ,[ColumnName]
        ,[Value]
        ,[Count] = CASE
            WHEN [ObjectType] IN ('USER TABLE') THEN COUNT(1)
            ELSE NULL
        END
        ,[SelectStatement]
    FROM @Results
    GROUP BY [ObjectType], [ObjectName], [ColumnName], [Value], [SelectStatement]
    ORDER BY [Value];
END;

/********************** STRING_SPLIT() FUNCTION **********************    
CREATE FUNCTION STRING_SPLIT (
    @Expression nvarchar(4000)
    ,@Delimiter nvarchar(100)
)
RETURNS @Ret TABLE ([value] NVARCHAR(4000))
AS
BEGIN

    DECLARE @Start INT = 0, @End INT, @Length INT;
    SELECT @End = CHARINDEX(@Delimiter,@Expression), @Length = @End - @Start;

    IF @End <= 0
    BEGIN
        INSERT INTO @Ret ([value]) VALUES (@Expression);
    END
    ELSE
    BEGIN
        WHILE @Length >= 0
        BEGIN
            INSERT INTO @Ret ([value])
                SELECT ltrim(rtrim(substring(@Expression,@Start,@Length)));
    
            SELECT @Start = @End + LEN(@Delimiter)
            SELECT @End = CHARINDEX(@Delimiter,@Expression,@Start)
            IF @End < 1
                SELECT @End = LEN(@Expression) + 1;
            SELECT @Length = @End - @Start;
    
        END;
    END;
    RETURN;
END;

*********************************************************************/

DECLARE
@Tmp TABLE
(Id INT, Descrip
VARCHAR(500))

INSERT
@Tmp SELECT
1,'my VAT calculation is incorrect'

INSERT
@Tmp SELECT
2,'Private number'

INSERT
@Tmp SELECT
3,'Innnovation model'

INSERT
@Tmp SELECT
4,'ELEVATE'

INSERT
@Tmp SELECT
5,'total VAT'

INSERT
@Tmp SELECT
6,'VAT'

SELECT
* FROM
@Tmp WHERE
Descrip LIKE
'VAT'

SELECT
* FROM
@Tmp WHERE
Descrip LIKE
'%VAT'

SELECT
* FROM
@Tmp WHERE
Descrip LIKE
'%VAT%'

SELECT
* FROM
@Tmp WHERE
Descrip LIKE
'% VAT %'

SELECT
* FROM
@Tmp WHERE
Descrip LIKE
'% VAT'

USE
[WideWorldImporters]

GO

--Note : Before compiling this SP, search for sqlConnString and provide Databasename, username & password for R SQL connection

CREATE OR ALTER PROC usp_SearchString (  @SearchString NVARCHAR(MAX),

 @SearchType VARCHAR(4),

 @Match BIT,

 @IgnoreCase BIT,

 @SearchSQLMetadata CHAR(1),

 @SchemaName NVARCHAR(50),

 @ObjectlisttoSearch NVARCHAR(MAX),

 @SearchCollate NVARCHAR(500)

 )

/*************************************************************************

=================

INPUT PARAMETERS:

=================

@SearchString - String to be searched

@SearchType  - ES - Exact Search using R

 PS - Pattern Search using R

 MPS - Multi Pattern Search - OR condition using R

 NTLS - Normal T-SQL Like Search

@Match - 0 = LIKE Search, 1 = NOT LIKE Search

@IgnoreCase - 1 = case insensitive search, 0 = Case sensitive search (If @IgnoreCase IS NULL then default : case insensitive search)

@SearchSQLMetadata - Search sql definitions for presence of input string. 1 = Search, 0 = Don't Search

@SchemaName  - List of objects to be searched that fall under schema (Multiple schema's can be passed, separated by Comma)

@ObjectlisttoSearch  - List of objects to be searched (Multiple table's can be passed, separated by Comma)

--IF BOTH @ObjectlisttoSearch & @SchemaName ARE BLANK THEN ENTIRE DATABASE IS SEARCHED INCLUDING SQL DEFINITIONS

@SearchCollate - For @SearchType = NTLS if @IgnoreCase  = 0. To search based on particular collation, default - COLLATE Latin1_General_CS_AS

*****************************************************************************/

AS

BEGIN

SET NOCOUNT ON;

IF @SearchType IN ('ES','PS','MPS','NTLS')

 BEGIN

 DECLARE @ExecutedBy NVARCHAR(200) = CURRENT_USER

 DECLARE @Serv NVARCHAR(200) = CONCAT(CHAR(39),CHAR(39),@@SERVERNAME,CHAR(39),CHAR(39))

IF ISNULL(@SchemaName,'') <>
''  OR ISNULL(@ObjectlisttoSearch,'') <>
''

 BEGIN

/**** List of table columns to be searched  ****/

DECLARE @TableColList TABLE (Cols NVARCHAR(MAX),colname NVARCHAR(200),Tbl NVARCHAR(128),TblCol
NVARCHAR(100),ColType NVARCHAR(150))

INSERT @TableColList

SELECT


 CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp')


 THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,')
AS ',QUOTENAME(C.NAME))

 ELSE C.name END Columns  -- To cover poor data type conversions when passed to R dataframe

 ,C.name

 ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName

 ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name,'.',C.name)
TblCol

 ,TY.name

FROM Sys.tables T

JOIN sys.columns C

ON T.object_id = C.object_id

JOIN sys.types TY


ON C.[user_type_id] = TY.[user_type_id]

-- Ignore the datatypes that are not required


WHERE TY.name NOT IN ('geography','varbinary','binary','text',
'ntext',
'image', 'hierarchyid',
'xml',
'sql_variant')

AND (Schema_name(T.schema_id) IN (SELECT value FROM STRING_SPLIT(@SchemaName,
','))

OR CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) IN (SELECT value FROM STRING_SPLIT(@ObjectlisttoSearch,
',')))

 END ELSE


 BEGIN

 INSERT @TableColList

SELECT


 CASE WHEN TY.name IN ('date','datetime2','datetimeoffset','time','timestamp')


 THEN CONCAT('TRY_CONVERT(','VARCHAR(MAX),',C.name,')
AS ',QUOTENAME(C.NAME))

 ELSE C.name END Columns  -- To cover poor data type conversions when passed to R dataframe

 ,C.name

 ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name) TableName

 ,CONCAT(SCHEMA_NAME(T.SCHEMA_ID),'.',T.name,'.',C.name)
TblCol

 ,TY.name

FROM Sys.tables T

JOIN sys.columns C

ON T.object_id = C.object_id

JOIN sys.types TY


ON C.[user_type_id] = TY.[user_type_id]

-- Ignore the datatypes that are not required


WHERE TY.name NOT IN ('geography','varbinary','binary','text',
'ntext',
'image', 'hierarchyid',
'xml',
'sql_variant')

 END

DROP TABLE IF EXISTS #ExportTablesList

CREATE TABLE #ExportTablesList (Rn BIGINT IDENTITY(1,1),cols
NVARCHAR(500),colname NVARCHAR(200),tbl NVARCHAR(200),ColType
NVARCHAR(200))

IF @SearchSQLMetadata =
1 OR (@SearchSQLMetadata <>
0 AND (ISNULL(@SchemaName,'') =
''  AND ISNULL(@ObjectlisttoSearch,'') =
''))

 BEGIN

 INSERT #ExportTablesList (cols,tbl,ColType) SELECT
'CONCAT(''<'',object_schema_name(sm.object_id),''.'',object_name(sm.object_id),''|'',o.type_desc
COLLATE Latin1_General_100_CI_AS,''>'',sm.definition) AS definition'

 ,'sys.sql_modules AS sm  JOIN sys.objects AS o ON sm.object_id = o.object_id'

 ,'sql_modules'

 END


--Deduplication of object list

;WITH dedup

AS

(

SELECT *,ROW_NUMBER()OVER(PARTITION BY Tbl,Cols ORDER BY Cols) Rn FROM @TableColList

)

INSERT INTO #ExportTablesList


SELECT cols,colname,tbl,ColType FROM dedup

WHERE Rn =
1

AND tbl <>
'dbo.Tbl_SearchString'

 /**** List of table columns to be searched  ****/

 IF (SELECT COUNT(1) FROM #ExportTablesList) <>
0

 BEGIN

 --Table to hold search output

IF NOT EXISTS (SELECT
1 FROM sys.tables WHERE name =
'Tbl_SearchString')

BEGIN

CREATE TABLE
[dbo].[Tbl_SearchString] (

[RunId] FLOAT,

[SearchIndex] BIGINT,


[SearchValue] NVARCHAR(MAX),


[NoOfOccurance] FLOAT,


[ObjectName] NVARCHAR(200),


[ColumnNameORDefinition] NVARCHAR(200),


[SqlDatatype] NVARCHAR(200),


[InputParameter] NVARCHAR(800),

[ExecutedBy] NVARCHAR(200),

[ExecutedAt] DATETIME

)

END

DECLARE @RunId FLOAT

SELECT @RunId = COALESCE(MAX([RunId]),0)+1
FROM [dbo].[Tbl_SearchString]

--Processing to store input parameters

DECLARE @Input NVARCHAR(MAX) = CONCAT(

 '@SearchString > '
,CASE WHEN @SearchString = ''
OR @SearchString IS NULL THEN 'NULL'
ELSE @SearchString END

,',@SearchType > ' 
,CASE WHEN @SearchType = ''
OR @SearchType IS NULL THEN 'NULL'
ELSE @SearchType END

,',@Match > '
,COALESCE(@Match,0)

,',@IgnoreCase > ' 
,COALESCE(@IgnoreCase,1)

,',@SearchSQLMetadata > ' 
,CASE WHEN @SearchSQLMetadata = ''
OR @SearchSQLMetadata IS NULL THEN 'NULL'
ELSE @SearchSQLMetadata END

,',@SchemaName > ' 
,CASE WHEN @SchemaName = ''
OR @SchemaName IS NULL THEN 'NULL'
ELSE @SchemaName END

,',@ObjectlisttoSearch > ' 
,CASE WHEN @ObjectlisttoSearch = ''
OR @ObjectlisttoSearch IS NULL THEN 'NULL'
ELSE @ObjectlisttoSearch END)

--By
default case insensitive search

SELECT @IgnoreCase = COALESCE(@IgnoreCase,1)

--By
default LIKE search

SELECT @Match = COALESCE(@Match,0)

IF @SearchType =
'NTLS'

BEGIN

DECLARE @SearchStrings TABLE (Id INT IDENTITY(1,1),String
NVARCHAR(MAX))

INSERT @SearchStrings

SELECT value FROM STRING_SPLIT(@SearchString,
'|')

 UPDATE #ExportTablesList SET Tbl =
'sys.sql_modules', colname =
'definition'

 WHERE ColType =
'sql_modules'

 SET @SearchCollate = CASE WHEN @SearchCollate =
'' THEN NULL ELSE @SearchCollate END

 DECLARE @COLLATE NVARCHAR(100)

 SET @COLLATE = CASE WHEN @IgnoreCase =
0 THEN CASE WHEN @SearchCollate =
'' OR @SearchCollate IS NULL THEN
' COLLATE Latin1_General_CS_AS '

 ELSE CONCAT(' COLLATE ',@SearchCollate,'
') END

 ELSE CHAR(32) END

 DECLARE @SearchOperator NVARCHAR(100)

 SET @SearchOperator = CASE WHEN @Match =
1 THEN
' NOT LIKE ' ELSE
' LIKE ' END

 DECLARE @WHEREClause NVARCHAR(MAX)

;WITH CTE

AS

(

SELECT 
'SearchValue '+ @SearchOperator +''''+String+''''+@COLLATE
WhereClause  FROM @SearchStrings

)

SELECT @WHEREClause = STUFF(


(SELECT 
' OR ' + WhereClause FROM

(SELECT WhereClause FROM CTE ) AS T FOR XML PATH('')),2,2,'')


END

SET @SearchString  = CASE WHEN @SearchType =
'ES' THEN REPLACE(@SearchString,'"','') ELSE @SearchString
END

 /**** Loop through above Objects list and execute R script ****/

 DECLARE @I INT =
1

 ,@SQL NVARCHAR(MAX) = N''

 ,@RScript NVARCHAR(MAX) = N''

 ,@tblname NVARCHAR(128)

 ,@Colname NVARCHAR(200)

 ,@Sqltype NVARCHAR(100)

 WHILE @I <= (SELECT MAX(Rn) FROM #ExportTablesList)

 BEGIN

 SELECT @SQL = CONCAT('SELECT ',Cols,'
FROM ',tbl)

 ,@tblname = Tbl

 ,@Colname = CASE WHEN @SearchType IN ('ES','PS')
THEN cols ELSE colname END

 ,@Sqltype = ColType

 FROM #ExportTablesList WHERE Rn = @I

IF @SearchType IN ('ES','PS','MPS')

BEGIN

SET @RScript = '

 #Provide DB credential detail for storing output in a table

 sqlConnString <-
"Driver=SQL Server;Server=serv; Database=WideWorldImporters;Uid=sa;Pwd=password"

#function to count no of occurences


 countCharOccurrences <- function(char,string,Type) {

 if (Type =="ES")

 {

 Boundchar <- paste0("\b",char,"\b",sep
="")

 string1
<- gsub(Boundchar,"",string,ignore.case=IgnoreCase)

 }

 string1
<- gsub(char,"",string,ignore.case=IgnoreCase)

 return ((nchar(string) - nchar(string1))/nchar(char))

 }

 #getting input dataset column name into a variable
"c"

 c <- colnames(InputDataSet)

 if (SearchType ==
"ES")

 {

 ExactString <- paste0("\b",SearchString,"\b",sep
="")

 Output <-  as.data.frame(grep(ExactString,InputDataSet[[c]],ignore.case = IgnoreCase,invert
= Match))

 colnames(Output)[1] <-
"SearchIndex"

 Output$SearchValue <- grep(ExactString,InputDataSet[[c]],ignore.case = IgnoreCase,value = TRUE,invert
= Match)

 Output$NoOfOccurance <- countCharOccurrences(SearchString,Output$SearchValue,SearchType)


 }

 if (SearchType ==
"PS" || SearchType ==
"MPS")

 {

 Output <-  as.data.frame(grep(SearchString,InputDataSet[[c]],ignore.case = IgnoreCase,invert
= Match))

 colnames(Output)[1] <-
"SearchIndex"

 Output$SearchValue <- grep(SearchString,InputDataSet[[c]],ignore.case = IgnoreCase,value = TRUE,invert
= Match)

 if (SearchType ==
"PS") {

 Output$NoOfOccurance <- countCharOccurrences(SearchString,Output$SearchValue,SearchType) }

 }

 Output$ObjectName <- rep(tblname,nrow(Output))

 Output$ColumnNameORDefinition <- rep(c,nrow(Output))

 Output$SqlDatatype <- rep(Sqltype,nrow(Output))

 Output$ObjectName[Output$SqlDatatype ==
"sql_modules"] <-
"sql_modules"

 Output$InputParameter <- rep(Input,nrow(Output))

 Output$ExecutedBy <- rep(ExecutedBy,nrow(Output))

 Output$ExecutedAt <- rep(format(Sys.time(),usetz = FALSE),nrow(Output))

 Output$RunId <- rep(RunId,nrow(Output))

 sqlDS <- RxSqlServerData(connectionString = sqlConnString,table =
"Tbl_SearchString")

 rxDataStep(inData = Output, outFile = sqlDS,append =
"rows")

 '

EXEC  sp_execute_external_script

 @language = N'R'

 ,@script = @RScript

 ,@input_data_1
= @SQL

 ,@params = N'@SearchString NVARCHAR(MAX),@SearchType VARCHAR(4),@Match
BIT,@IgnoreCase BIT,@Input NVARCHAR(MAX)

 ,@tblname NVARCHAR(128),@Sqltype NVARCHAR(150),@ExecutedBy
NVARCHAR(200),@RunId FLOAT

 ,@Serv NVARCHAR(200)'

 ,@SearchString = @SearchString

 ,@SearchType = @SearchType

 ,@Match = @Match

 ,@IgnoreCase = @IgnoreCase

 ,@Input = @Input

 ,@tblname = @tblname

 ,@Sqltype = @Sqltype

 ,@ExecutedBy = @ExecutedBy

 ,@RunId = @RunId

 ,@Serv = @Serv

END

IF @SearchType =
'NTLS'

BEGIN

INSERT
[dbo].[Tbl_SearchString]([RunId],[SearchIndex],[SearchValue],[ObjectName]

 ,[ColumnNameORDefinition],[SqlDatatype],[InputParameter],[ExecutedBy],[ExecutedAt])

EXEC ('SELECT '+@RunId+',SearchIndex,SearchValue,'''+@tblname+''','''+@Colname+''','''+@Sqltype+''','''+@Input+''','''+@ExecutedBy+''',
GETDATE()

FROM (SELECT ROW_NUMBER()OVER(ORDER BY (SELECT
1)) SearchIndex,'+@Colname+'
AS SearchValue FROM '+@tblname+

' ) Tmp WHERE '+@WHEREClause)

END

SET @I = @I +
1

END

 /**** Loop through above table list and execute R script ****/

 --Display final search result

 SELECT * FROM
[dbo].[Tbl_SearchString] WHERE RunId = @RunId AND ExecutedBy = CURRENT_USER

 END

 ELSE


 SELECT
'No valid objects passed in the InputParameter to search the string'
AS InvalidParameter

 END

 ELSE


 SELECT 'SearchType parameter is mandatory ES - Exact Search, PS - Pattern Search,MPS - Multi Pattern Search - OR condition

 ,NTLS - Normal T-SQL Like Search' AS InvalidParameter

 END

EXEC
usp_SearchString         @SearchString = 'VAT'

            ,@SearchType =
'ES'

  ,@Match = 0 
-- 0 = LIKE, 1 = NOT LIKE

  ,@IgnoreCase = 1 
-- 1 = Case insensitive, 0 = Case Sensitive

  ,@SearchSQLMetadata= 0
-- 1 = Search, 0 = Don't Search

  ,@SchemaName = ''

  ,@ObjectlisttoSearch = 'dbo.Tmp'

  ,@SearchCollate = ''

--To get deails of particular RunId

SELECT 
DISTINCT
RunId

 ,[ObjectName]

FROM
[WideWorldImporters].[dbo].[Tbl_SearchString]

WHERE
RunId = 12

SELECT 
A.*

 ,B.*

FROM
[WideWorldImporters].[dbo].[Tbl_SearchString] A

JOIN
(SELECT
row_number()over(order
by
(SELECT
'A')) Rn,* FROM
Warehouse.StockItemHoldings ) B --Change table name

 ON
A.SearchIndex = B.Rn

 AND
A.ObjectName = 'Warehouse.StockItemHoldings'
--Change table name

 AND
RunId = 12 --provide run id