Как найти слово в строке sql

In my previous article about T-SQL regular expressions, I have explained the LIKE operator, its usage and provided several examples with it. In this article, we are going to discuss the SUBSTRING, PATINDEX, and CHARINDEX functions of T-SQL. These functions can be used to perform pattern matching.

First, let me explain the SUBSTRING function and provide examples with it.

SUBSTRING

SQL Server SUBSTRING() function is used to extract the substring from the given input_string. It extracts the substring, starting from the specified position defined by the parameter.
Following is the syntax for the SUBSTRING()

SUBSTRING() function accepts following parameters:

1. Input_string. This parameter defines a string expression from which you want to extract the substring. This parameter can be text, character, or binary string.
2. Starting_position. This parameter defines a starting position from where you want to extract the substring. This parameter must be an integer value. You can use only one starting position parameter.
3. Length. This parameter defines a position of the character you want to extract from the input string. This parameter must be an integer value.

The following are the examples of the SUBSTRING function.

Example 1: Use SUBSTRING using literal string input

For example, I want to extract the word “DBA” from the input string “Nisarg is DBA.” In an input string, the position of the character D is eleven, and we want to extract three characters, therefore, the value of the “starting_position” parameter is 11, and the value of the “length” parameter is 3.

To do that, execute the following:

Following is the output:

Example 2: Use SUBSTRING within T-SQL Query

I have created a sample table named “IPDREGISTRATION” in the VSData database. In the table, there is a column named “IPDREGNO,” which represents patients’ registration number. The IPDREGNO is a unique number and its format is <IP> <Financial_Year><6 digit IncrementalNumber>. For example, IP1920000001.

I want to retrieve only six-digit numbers from the IPDREGNo column. According to the conditions, we want to trim the <IP> <Financial_Year> from the input column, therefore, the value of starting_position is seven and the value of length parameter will be six.
Execute the following query:

Following is the output:

Example 3: Extract the domain name from the email address

For example, I want to extract the domain name from the email address field of any table. To achieve that, we will use the substring and CHARINDEX() function. The following script can be used to extract the domain name from the email address.

As you can see in the above script, the CHARINDEX() function searches the string after @ and adds +1 to mark it as the start position of a string. Then it uses the LEN function to get the endpoint.

Following is the output:

Using this script, we can also find the count of an email address for each domain name. To do that we will use the COUNT function and GROUP BY clause. The following script does this task:

Following is the output:

CHARINDEX

SQL Server CHARINDEX() function is used to search the position of a substring inside an input string. Unlike SUBSTRING(), this function starts the search from a specified location and returns the position of the substring. If a substring is not found, it returns zero. CHARINDEX() function is used to perform case sensitive and case insensitive searches based on the collation specified in the query.
Following is the syntax of the CHARDINDEX() function.

CHARINDEX() accepts three arguments:

1. Substring. This argument defines the substring that you want to search within the input string.
2. Input_string. This argument defines the input string.
3. Start_location. This argument defines the location from which you want to start the search within the input string. The data type of this parameter is an integer, and this is an optional parameter. If this parameter is not specified, then the search starts from the beginning of the input string.

The following are the examples of CHARINDEX function.

Example 1: Use CHARINDEX using literal string input

For example, I want to check the position of the “example” substring from the input string “This is CHARINDEX example”. To do that, execute the following query:

Following is the output:

Now, execute the following query:

Following is the output:

Example 2: Perform case-sensitive search

As I mentioned above, we can perform a case-sensitive search using the CHARINDEX function. To do that we have to use COLLATE function within a query, For example, I want to get the position of “CHARINDEX” substring from the input string “This is CHARINDEX example” To do that execute the following query:

Following is the output:

As you can see from the above, we have used case-sensitive collate, therefore the position of the word “CHARINDEX” is zero. Now, execute the following query:

Following is the output:

As you can see, the query returned the position of the word “CHARINDEX” from the input string.

Example 3: Get the database file name using the T-SQL function

For example, I want to populate the database and log file name of all databases. To get the file names, we are going to use a dynamic management view named sys.master_files. Execute the following query to create a function.

Now, let me show you how to use this function. To get the file name, execute the following query:

Following is the output:

PATINDEX

The SQL Server PATINDEX() function returns the position of a pattern within an input string. Following is the syntax of PATINDEX():

The PATINDEX() function accepts two parameters:

1. String_Pattern. This parameter defines character expression that you want to find in the input string. In the pattern, it contains the wildcard characters like % and ‘_’. The wild-card characters are the same as those used in LIKE operators.

2. input_string. This parameter defines an input string in which you want to search the pattern.

The PATINDEX() function returns the position of the first occurrence of a pattern in a string. If a pattern is not found within a string, this function returns Zero. If you pass NULL as an input_parameter, it returns NULL. PATINDEX() searches pattern is based on the collation of the input parameter. We can use the COLLATE clause to use specific collation.

The following are the SQL Server PATINDEX() function examples.

Example 1: Use PATINDEX in a literal string input

Let’s consider a simple example of PATINDEX. For example, I want to find the position of the word “example” in the “Wild-card example” input string. To do that, execute the following query:

Following is the output:

Example 2: Use of PATINDEX with wildcards

We can use wild-card characters to find the position of a specific word within an input string. In the following example, we use % and _ wild-card to find the position of a word in which the first two characters are “Wi”, followed by any other two characters and -card in input string “PATINDEX Wild-card example”.

SQL SELECT WHERE field contains words

I need a select which would return results like this:

And I need all results, i.e. this includes strings with ‘word2 word3 word1’ or ‘word1 word3 word2’ or any other combination of the three.

All words need to be in the result.

15 Answers 15

Rather slow, but working method to include any of words:

If you need all words to be present, use this:

If you want something faster, you need to look into full text search, and this is very specific for each database type.

Note that if you use LIKE to determine if a string is a substring of another string, you must escape the pattern matching characters in your search string.

If your SQL dialect supports CHARINDEX , it’s a lot easier to use it instead:

Also, please keep in mind that this and the method in the accepted answer only cover substring matching rather than word matching. So, for example, the string ‘word1word2word3’ would still match.

Auxiliar Function

Query Example

Search words word1 , word2 , word3 into MyTable.Column1 :

Instead of SELECT * FROM MyTable WHERE Column1 CONTAINS ‘word1 word2 word3’ , add And in between those words like:

For selecting phrases, use double quotes like:

P.S.: You have to first enable Full Text Search on the table before using contains keyword. For more details, see Get Started with Full-Text Search.

Changed OR to AND based on edit to question.

If you are using Oracle Database then you can achieve this using a contains query. Contains queries are faster than like queries.

If you need all of the words

If you need any of the words

Contains need index of type CONTEXT on your column.

If you just want to find a match.

To get exact match. Example: (‘;a;ab;ac;’,’;b;’) will not get a match.

One of the easiest ways to achieve what is mentioned in the question is by using CONTAINS with NEAR or ‘

‘. For example, the following queries would give us all the columns that specifically include word1, word2 and word3.

In addition, CONTAINSTABLE returns a rank for each document based on the proximity of "word1", "word2" and "word3". For example, if a document contains the sentence, "The word1 is word2 and word3," its ranking would be high because the terms are closer to one another than in other documents.

We can also use proximity_term to find columns where the words are inside a specific distance between them inside the column phrase.

О строковых функциях SQL или как манипулировать текстом в базе данных MySQL

Здравствуйте, уважаемые читатели блога webcodius.ru. Сегодня я хотел бы поговорить о языке SQL, а в частности о функциях для обработки текста. Для создания и управления сайтом часто бывает не обязательно знание языка SQL. Системы управления контентом позволяют редактировать контент сайта без написания запросов. Но хотя бы поверхностное знакомство с структурированным языком запросов поможет вам значительно ускорить модификацию и управление данными в базе данных вашего сайта.

Передо мной частенько возникают задачи: удалить часть текста из текстовых полей базы данных, объединить строковые данные или еще что-нибудь связанное с текстом. Делать все это через админские панели сайтов очень неудобно и муторно. Гораздо проще бывает написать запрос к базе данных выполняющий все эти действия за пару секунд.

Символьные функции в языке sql

Начнем по порядку с самого простого. Первой рассмотрим строковую функцию ASCII, которая используется для определения ASCII-кода текстовых символов:

integer ASCII(str string)

Функция возвращает целое значение — ASCII-код первого левого символа строки str. В случае если строка str пустая возвращает 0 и NULL если строка str не существует.

SELECT ASCII ('t');
Результат: 116
SELECT ASCII ('test');
Результат: 116
SELECT ASCII (1);
Результат: 49

Далее функция ORD, которая также определяет ASCII- код символов, но может обрабатывать также многобайтовые символы:

integer ORD(str string)

Если первый левый символ строки str многобайтовый, то возвращает его код в формате: ((первый байт ASCII- код)*256+(второй байт ASCII -код))[*256+третий байт ASCII -код. ]. В случае если первый левый символ строки str не является многобайтовым, работает как функция ASCII — возвращает его ASCII-код.

SELECT ORD ('test');
Результат: 116

Функция CHAR, тесно связанная с функцией ASCII и выполняет обратное действие:

string CHAR(int integer, . )

Функция CHAR возвращает строку символов по их ASCII-кодам. Если среди значений встречается значение NULL, то оно пропускается.

SELECT CHAR ( 116, '101', 115, '116' );
Результат: 'test'

SQL функции для объединения строк

Одна из самых популярных категорий функций. Ведь частенько бывает нужно объединить значения нескольких полей таблиц базы данных сайта. В языке SQL есть сразу несколько функций для конкатенации строк.

string CONCAT(str1 string, str2 string. )

Функция возвращает строку, созданную путем объединения аргументов. Можно указывать более двух аргументов. Если один из аргументов является NULL, то и возвращаемый результат будет NULL. Числовые значения преобразуются в строку.

SELECT CONCAT ('Hello', ' ', 'world', '!');
Результат: 'Hello world!'
SELECT CONCAT ('Hello', NULL, 'world', '!');
Результат: NULL
SELECT CONCAT ('Число пи', '=', 3.14);
Результат: 'Число пи=3.14'

Как видно из примеров, строки объединяются без разделителей. Для того чтобы разделить слова в первом примере в качестве аргумента приходится использовать пробел. Если бы слов было больше, то каждый раз вставлять пробелы было бы не очень удобно.

Для таких случаев существует функция CONCAT_WS:

string CONCAT_WS(separator string, str1 string, str2 string. )

Функция объединяет строки как и функция CONCAT, но вставляет между аргументами разделитель separator. В случае если аргумент separator является NULL, то и результат будет NULL. Аргументы строки равные NULL пропускаются.

SELECT CONCAT_WS (' ', 'Иванов', 'Иван', 'Иванович');
Результат: 'Иванов Иван Иванович'
SELECT CONCAT_WS (NULL, 'Иванов', 'Иван', 'Иванович');
Результат: NULL
SELECT CONCAT_WS (' ', 'Иванов', NULL, 'Иван', 'Иванович');
Результат: ''Иванов Иван Иванович'

В случае объединения большого количества строк, которые необходимо отделять разделителем, функция CONCAT_WS гораздо удобнее функции CONCAT.

Иногда бывает необходимо удлинить строку до определенного количества символов за счет повторения какого-либо символа. Это тоже своего рода объединение строк. Для этого можно использовать функции LPAD и RPAD. Функции имеют следующий синтаксис:

string LPAD(str string, len integer, padstr string)
string RPAD(str string, len integer, padstr string)

Функция LPAD возвращает строку str дополненную слева строкой padstr до длины len. Функция RPAD выполняет тоже самое, только удлинение происходит с правой стороны.

SELECT LPAD ('test', 10, '.');
Результат: . test
SELECT RPAD ('test', 10, '.');
Результат: test.

В данных функциях необходимо обратить внимание на параметр len, который ограничивает количество выводимых символов. Поэтому если длина строки str будет больше чем параметр len, то строка будет обрезана:

SELECT LPAD ('test', 3, '.');
Результат: tes

Определение длины строки в sql запросах

Для определения количества символов в строке в языке SQL отвечает функция LENGTH — длина строки:

integer LENGTH(str string)

Функция возвращает целое число равное количеству символов в строке str.

SELECT LENGTH ('test');
Результат: 4

В случае использования многобайтовых кодировок функция LENGTH выдает не правильный результат. Например в случае если задана кодировка unicode, то запрос:

SELECT LENGTH ('тест');

вернет 8. Что, легко заметить, в два раза больше реального количества символов. В этом случае нужно использовать функцию CHAR_LENGTH:

integer CHAR_LENGTH(str string)

Функция также возвращает длину строки str и поддерживает многобайтовые символы.

SELECT CHAR_LENGTH ('тест');
Результат: 4

Поиск подстроки в строке средствами sql

Для вычисления позиции подстроки в строке в языке sql существует несколько функций. Первая, которую мы рассмотрим, функция POSITION:

integer POSITION(substr string IN str string)

Возвращает номер позиции первого вхождения подстроки substr в строке str и возвращает 0 если подстрока не найдена. Функция POSITION может работать с многобайтовыми символами.

SELECT POSITION ('cd' IN 'abcdcde');
Результат: 3
SELECT POSITION ('xy' IN 'abcdcde');
Результат: 0

Следующая функция LOCATE позволяет начинать поиск подстроки с определенной позиции:

integer LOCATE(substr string, str string, pos integer)

Возвращает позицию первого вхождения подстроки substr в строке str, начиная с позиции pos. Если параметр pos не задан, то поиск осуществляется с начала строки. Если подстрока substr не найдена, то возвращает 0. Поддерживает многобайтовые символы.

SELECT LOCATE ('cd', 'abcdcdde', 5);
Результат: 5
SELECT LOCATE ('cd', 'abcdcdde');
Результат: 3

Аналогом функций POSITION и LOCATE является функция INSTR:

integer INSTR(str string, substr string)

Также как и функции выше возвращает позицию первого вхождения подстроки substr в строке str. Единственное отличие от функций POSITION и LOCATE то, что аргументы поменяны местами.

Далее рассмотрим функции, которые помогают получить подстроку.

Первыми рассмотрим сразу две функции LEFT и RIGHT, которые похожи по своему действию:

string LEFT(str string, len integer)
string RIGHT(str string, len integer)

Функция LEFT возвращает len первых символов из строки str, а функция RIGHT столько же последних. Поддерживают многобайтовые символы.

SELECT LEFT ('Москва', 3);
Результат: Мос
SELECT RIGHT ('Москва', 3);
Результат: ква

Далее рассмотрим одинаковые по итоговому результату функции SUBSTRING и MID:

string SUBSTRING(str string, pos integer, len integer)
string MID(str string, pos integer, len integer)

Функции позволяют получить подстроку строки str длиною len символов с позиции pos. В случае если параметр len не задан, то возвращается вся подстрока начиная с позиции pos.

SELECT SUBSTRING ('г. Москва — столица России', 4, 6);
Результат: Москва
SELECT SUBSTRING ('г. Москва — столица России', 4);
Результат: Москва — столица России

Примеры с функцией MID не привожу, потому что результаты будут аналогичные.

Интересная функция SUBSTRING_INDEX:

string SUBSTRING_INDEX(str string, delim string, count integer)

Функция возвращает подстроку строки str, полученную путем удаления символов, идущих после разделителя delim, находящимся в позиции count. Параметр count может быть как положительным, так отрицательным. Если count положительный, то отсчет позиции разделителя будет вестись слева и удаляться будут символы находящиеся справа от разделителя. Если count отрицательный, то отсчет позиции разделителя ведется справа и удаляются символы находящиеся слева от разделителя. Возможно, описание получилось слишком запутанным, но на примерах станет понятней.

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 1);
Результат: www

В данном примере функция находит, первое вхождения символа точки в строке «www.mysql.ru» и удаляет все символы, идущие после нее, включая сам разделитель.

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', 2);
Результат: www.mysql

Здесь функция ищет второе вхождение точки, удаляет все символы справа от нее и возвращает получившуюся подстроку. И еще один пример с отрицательным значением параметра count:

SELECT SUBSTRING_INDEX ('www.mysql.ru', '.', -2);
Результат: mysql.ru

В этом примере функция SUBSTRING_INDEX ищет вторую точку, отсчитывая позицию справа, удаляет символы слева от нее и выдает полученную подстроку.

Удаление пробелов из строки

Для удаления лишних пробелов из начала и конца строки в языке SQL есть три функции.

string LTRIM(str string)

Удаляет с начала строки str пробелы и возвращает результат.

string RTRIM(str string)

Также удаляет пробелы из строки str, только с конца. Обе функции поддерживают многобайтовые символы.

SELECT LTRIM (' текст ');
Результат: 'текст '
SELECT RTRIM (' текст ');
Результат: ' текст'

И третья функция TRIM позволяет сразу удалять пробелы из начала и из конца строки:

string TRIM([[BOTH | LEADING | TRAILING] [remstr] string FROM] str string)

Параметр str обязательный, остальные параметры не обязательные. В случае если задан только один параметр str, то возвращает строку str удалив пробелы из начала и конца строки одновременно.

SELECT TRIM (' текст ');
Результат: 'текст'

С помощью пара метра remstr можно задавать символы или подстроки, которые будут удаляться из начала и конца строки. С помощью управляющих параметров BOTH, LEADING, TRAILING можно задавать откуда будут удаляться символы:

• BOTH — удаляет подстроку remstr с начала и с конца строки;
• LEADING — удаляет remstr с начала строки;
• TRAILING — удаляет remstr с конца строки.

SELECT TRIM (BOTH 'а' FROM 'текст');
Результат: 'текст'
SELECT TRIM (LEADING 'а' FROM 'текстааа');
Результат: 'текстааа'
SELECT TRIM (TRAILING 'а' FROM 'ааатекст');
Результат: 'ааатекст'

Функция SPACE позволяет получить строку состоящую из определенного количества пробелов:

string SPACE(n integer)

Возвращает строку, которая состоит из n пробелов.

Функция REPLACE нужна для замены заданных символов в строке:

string REPLACE(str string, from_str string, to_str string)

Функция заменяет в строке str все подстроки from_str на to_str и возвращает результат. Поддерживает многобайтные символы.

SELECT REPLACE ( 'замена подстроки', 'подстроки', 'текста' )
Результат: 'замена текста'

string REPEAT(str string, count integer)

Функция возвращает строку, которая состоит из count повторений строки str. Поддерживает многобайтовые символы.

Функция REVERSE переворачивает строку:

string REVERSE(str string)

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

SELECT REVERSE ('текст');
Результат: 'тскет'

Функция INSERT для вставки подстроки в строку:

string INSERT(str string, pos integer, len integer, newstr string)

Возвращает строку полученную в результате вставки в строку str подстроки newstr с позиции pos. Параметр len указывает сколько символов будет удалено из строки str, начиная с позиции pos. Поддерживает многобайтовые символы.

SELECT INSERT ('text', 2, 5, 'MySQL');
Результат: 'tMySQL'
'SELECT INSERT ('text', 2, 0, 'MySQL');
Результат: 'tMySQLext'
SELECT INSERT ('вставка текста', 2, 7, 'MySQL');
Результат: 'SELECT INSERT ('вставка текста', 2, 7, 'MySQL');'

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

string LCASE(str string) и string LOWER(str string)

Обе функции заменяют в строке str заглавные буквы на прописные и возвращают результат. И та и другая поддерживают многобайтовые символы.

SELCET LOWER ('АБВГДеЖЗиКЛ');
Результат:'абвгдежзикл'

Если же наоборот необходимо прописные буквы заменить заглавными, то также можно применить одну из двух функцийй:

string UCASE(str string) и string UPPER (str string)

Функции возвращают строку str, заменив все прописные символы на заглавные. Также поддерживают многобайтовые символы.
Пример:

SELECT UPPER ('Абвгдежз');
Результат: 'АБВГДЕЖЗ'

Строковых функций в языке SQL немного больше, чем рассмотрено в данной статье. Но так как даже большинство рассмотренных здесь функций используются редко, я закончу их рассмотрение. В следующих статьях я постараюсь рассмотреть реальные практические примеры использования строковых функций SQL. Поэтому не забудьте подписаться на обновления блога. До новых встреч!