SQL-Ex blog

SUBSTRING

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

1.

SUBSTRING (строка, позиция)

Эта функция возвращает подстроку из строки, начиная с указанной позиции. Эта функция наиболее полезна, когда необходимо задать точную позицию начала строки. Сначала давайте определимся с позициями в строке. Вот пример строки:

THIS IS A SAMPLE

Позиции в SUBSTRING всегда начинаются с 1. Поэтому буква 'T' в 'THIS' будет первой буквой. Итак, позициями в строке будут следующие:

T = 1

H = 2

I = 3

S = 4

пробел = 5

I = 6

S = 7

пробел = 8

A = 9

пробел = 10

S = 11

A = 12

M = 13

P = 14

L = 15

E = 16

Отметим, что мы собираемся использовать эту строку повсюду в статье. Поэтому мы будем часто обращаться к этому примеру. Пусть теперь мы хотим извлечь только слово “Sample” из всей строки. В нашем случае мы могли бы использовать позицию 11 для извлечения этого слова. Тогда функция будет выглядеть как-то так:

SUBSTRING('THIS IS A SAMPLE',11)

Будет получен следующий результат:



Убедитесь, что вы не начинаете с 10 - позиции за действительной начальной точкой, т.к. это даст вам ненужный лишний символ, которым в нашем случае является пробел (' ').

MySQL также дает возможность использовать в качестве позиции отрицательные целые числа. Давайте для начала посмотрим, как отрицательная позиция будет выглядеть в нашем примере:

T = -16

H = -15

I = -14

S = -13

пробел = -12

I = -11

S = -10

пробел = -9

A = -8

пробел = -7

S = -6

A = -5

M = -4

P = -3

L = -2

E = -1

Как видно номера позиций начинаются с последнего символа в направлении начального символа. Давайте рассмотрим пример. Например, мы хотим извлечь из строки слово “SAMPLE”. Для этого нам нужно использовать следующую функцию:

SUBSTRING('THIS IS A SAMPLE',-6)

Будет получен следующий результат:



Тем самым мы можем использовать отрицательное целое для получения тех же самых результатов. Однако иногда несколько затруднительно подсчитывать отрицательные значения. Поэтому я бы предостерег вас от их использования.

2.

SUBSTRING (строка FROM позиция)

Это вариация предыдущего примера для SUBSTRING(строка, позиция). Работает точно так же. Требуется указать строку, из которой извлекается подстрока. Ключевое слово FROM определяет позицию, откуда начинается подстрока. Как и в предыдущем примере, будем извлекать слово “SAMPLE”, тогда функция принимает вид:

SUBSTRING('THIS IS A SAMPLE' FROM 11)

В этом синтаксисе мы также можем использовать отрицательные целые значения:

SUBSTRING('THIS IS A SAMPLE' FROM -6)

Эта вариация более в стиле SQL, где мы пишем запрос типа "select * from xyz". Для разработчиков это будет более привычно. Однако оба эти варианта имеют ограничение. Они могут извлекать только строку, начиная с определенной позиции. Это не дает возможности получить строку от некоторой начальной точки до некоторой заданной конечной точки.

3.

SUBSTRING (строка, позиция, длина)

Это та вариация, которая позволит вам извлечь строку точной длины. В этом варианте мы задаем "строку" из которой требуется получить подстроку, "позицию" откуда начинать, и "длину", определяющую количество требуемых символов. Давайте вернемся к примеру “THIS IS A SAMPLE”. Допустим, я хочу извлечь 'IS' из строки. Вот способ сделать это:

SUBSTRING('THIS IS A SAMPLE',6,2)

Результат:



Рассмотрим другой пример. Требуется извлечь “A SAMPLE” из всей строки. Тогда функция будет следующей:

SUBSTRING('THIS IS A SAMPLE', 9, 

Здесь 9 - это начальная позиция, начиная с которой извлекается 8 символов для получения нижеприведенного результата:



Подобно вышеприведенным примерам, мы можем даже использовать отрицательное целое в этом варианте для получения той же строки:

SUBSTRING('THIS IS A SAMPLE', -11,2)

Вот извлеченная подстрока:



-11 - это позиция справа начальной точки. И будет извлекаться 2 символа вправо, как показано выше. Нужно быть внимательным при указании длины, т.к. она она отсчитывается вправо от заданной позиции.

4.

SUBSRTING (строка FROM позиция FOR длина)

Подобно предыдущей вариации мы можем использовать другой синтаксис, где задается "строка", откуда извлекается подстрока, ключевое слово FROM с указанием начальной "позиции" и ключевое слово FOR для указания "длины", т.е. числа символов.

Повторим предыдущий пример для извлечения подстроки "IS" из всей строки. Тогда нам следует использовать следующую функцию:

SUBSTRING('THIS IS A SAMPLE' FROM 6 FOR 2)

Будет получен следующий результат:



Вы также можете использовать отрицательные целые числа, как это делалось ранее. Например,

SUBSTRING('THIS IS A SAMPLE', FROM -11,2)

Это даст извлечение "IS" из всей строки.



Следует иметь в виду:

1. В SUBSTRING(str, pos) указываются положительные или отрицательные значения. Задание 0 приведет к ошибке.


2. В SUBSTRING(str, pos, length) длина должна быть положительным целым числом. Задание 0 не вернет значения, т.к. это означает, что вы пытаетесь извлечь 0 символов.


3. Если никакие значения не указываются для параметров pos и length, функция вернет NULL.

SUBSTR

SUBSTR - это синоним, который может использоваться вместо substring:

SUBSTR(string, pos)

SUBSTR(string FROM pos)

SUBSTR(string, pos, len)

SUBSTR(string FROM pos FOR len)

SUBSTRING_INDEX

SUBSTRING_INDEX(String, delimiter, count)

Здесь "String" - это исходная строка.

"delimiter" - символ, который имеет множественное вхождение во всей строке; "count" - число вхождений для получения строки до и после него. Если count положителен, то будут извлекаться строки слева в количестве, заданном count. Если count отрицателен, извлекаться строки будут справа.

Эта функция может использоваться в случае, когда нам нужно извлечь информацию на основе позиции разделителя. Например, у нас есть URL сайта, и требуется извлечь доменное имя из этого URL. Скажем, требуется извлечь “sqlshack” из “https://www.sqlshack.com/”. Мы можем достичь этого в два приема:

SUBSTRING_INDEX('https://www.sqlshack.com/','.',2)

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



Однако это не дает нам доменного имени. Поэтому мы должны использовать еще одну функцию SUBSTRING_INDEX, чтобы получить значения справа от вхождения разделителя:

SUBSTRING_INDEX(SUBSTRING_INDEX('https://www.sqlshack.com/','.',2),'.',-1)

Это даст требуемый результат:



Таким способом вы можете использовать substring в разных сочетаниях и комбинациях с разными функциями для извлечения подстроки.

Ссылки по теме
1. Пример применения функции Substring в T-SQL, R и Python
2. Функции работы со строками в SQL Server, Oracle и PostgreSQL