Как обрезать строку в sql

Как обрезать строку в sql

The SUBSTR functions return a portion of char , beginning at character position , substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set. SUBSTRB uses bytes instead of characters. SUBSTRC uses Unicode complete characters. SUBSTR2 uses UCS2 code points. SUBSTR4 uses UCS4 code points.

If position is 0, then it is treated as 1.

If position is positive, then Oracle Database counts from the beginning of char to find the first character.

If position is negative, then Oracle counts backward from the end of char .

If substring_length is omitted, then Oracle returns all characters to the end of char . If substring_length is less than 1, then Oracle returns null.

char can be any of the data types CHAR , VARCHAR2 , NCHAR , NVARCHAR2 , CLOB , or NCLOB . The exceptions are SUBSTRC , SUBSTR2 , and SUBSTR4 , which do not allow char to be a CLOB or NCLOB . Both position and substring_length must be of data type NUMBER , or any data type that can be implicitly converted to NUMBER , and must resolve to an integer. The return value is the same data type as char , except that for a CHAR argument a VARCHAR2 value is returned, and for an NCHAR argument an NVARCHAR2 value is returned. Floating-point numbers passed as arguments to SUBSTR are automatically converted to integers.

Oracle Database Globalization Support Guide for more information about SUBSTR functions and length semantics in different locales

Appendix C in Oracle Database Globalization Support Guide for the collation derivation rules, which define the collation assigned to the character return value of SUBSTR

SUBSTRING (Transact-SQL)

Возвращает часть символьного, двоичного, текстового или графического выражения в SQL Server.

Синтаксис

Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям.

Аргументы

expression
Выражение типа character, binary, text, ntext или image.

start
Целое число или выражение типа bigint, указывающее начальную позицию возвращаемых символов. (Нумерация начинается с 1, то есть первый символ в выражении имеет позицию 1.) Если аргумент start имеет значение меньше 1, то возвращаемое выражение начинается с первого символа, который указан в аргументе expression. В этом случае количество возвращаемых символов является наибольшим значением либо суммы start + length– 1, либо 0. Если значение start больше количества символов в выражении значения, возвращается выражение нулевой длины.

length
Положительное целое число или выражение типа bigint, указывающее количество символов выражения expression, которое будет возвращено. Если значение length отрицательно, возникает ошибка и выполнение инструкции прерывается. Если сумма start и length больше количества символов в expression, то возвращается целочисленное выражение значения, начинающееся со значения start.

Типы возвращаемых данных

Возвращает символьные данные, если expression имеет один из поддерживаемых символьных типов данных. Возвращает двоичные данные, если аргумент expression имеет один из поддерживаемых двоичных типов данных. Возвращенная строка имеет тот же самый тип, как и заданное выражение. Исключения указаны в таблице.

Заданное выражение	Возвращаемый тип
char/varchar/text	varchar
nchar/nvarchar/ntext	nvarchar
binary/varbinary/image	varbinary

Remarks

Значения start и length должны быть указаны в виде количества символов для типов данных ntext, char или varchar и байтов для типов данных text, image, binary или varbinary.

Аргумент expression должен иметь тип varchar(max) или varbinary(max) , если аргумент start или length содержит значение, превышающее 2 147 483 647.

Дополнительные символы (суррогатные пары)

При использовании параметров сортировки дополнительных символов (SC) и start, и length обрабатывают каждую суррогатную пару в expression как один символ. Дополнительные сведения см. в статье Collation and Unicode Support.

Примеры

A. Использование SUBSTRING с символьной строкой

Следующий пример показывает, как получить часть символьной строки. Из таблицы sys.databases этот запрос возвращает имена системных баз данных в первом столбце, первую букву имени базы данных во втором столбце и третий и четвертый символы в последнем столбце.

name	Initial	ThirdAndFourthCharacters
master	m	st
tempdb	t	mp
model	m	de
msdb	m	db

Далее показано, как можно вывести второй, третий и четвертый символ строковой константы abcdef .

Б. Использование SUBSTRING с данными типа text, ntext или image

Для выполнения приведенных ниже примеров необходимо установить базу данных pubs.

В приведенном ниже примере показано, как вернуть первые 10 символов из каждого столбца данных text и image в таблице pub_info базы данных pubs . Данные text возвращаются как varchar, а данные image — как varbinary.

В приведенном ниже примере показано влияние функции SUBSTRING на данные типов text и ntext. Во-первых, пример создает новую таблицу в базе данных pubs под именем npub_info . Во-вторых, пример создает столбец pr_info в таблице npub_info из первых 80 символов столбца pub_info.pr_info и добавляет ü в качестве первого символа. Наконец, с помощью предложения INNER JOIN извлекаются все идентификационные номера издателей, а также обработанные функцией SUBSTRING значения столбцов типа text и ntext со сведениями об издателях.

Примеры: Azure Synapse Analytics и Система платформы аналитики (PDW)

В. Использование SUBSTRING с символьной строкой

Следующий пример показывает, как получить часть символьной строки. Из таблицы dbo.DimEmployee данный запрос возвращает фамилию в одном столбце и первую букву имени в другом.

В приведенном ниже примере показано, как получить второй, третий и четвертый символы строковой константы abcdef .

SQL-Ex blog

Функции работы со строками в SQL Server, Oracle и PostgreSQL

Строковые функции широко используются для манипуляции, извлечения, форматирования и поиска текста для типов данных char, nchar (unicode), varchar, nvarchar (unicode) и т.д. К сожалению, имеются некоторые отличия в строковых функциях SQL Server, Oracle и PostgreSQL, которые обсуждаются в этой статье.

Как всегда мы будет использовать свободно загружаемую с github базу данных Chinook, т.к. она доступна в форматах множества РСУБД. Она представляет собой имитацию магазина цифровых носителей с некоторым количеством данных, и вы можете загрузить ту версию, которая вам нужна, и получить все скрипты для создания структуры данных и операторы для вставки данных.

Строковые функции SQL для конкатенации строк

Начнем с самой простой и наиболее часто используемой операции — конкатенации двух (или большего числа) строк, и рассмотрим различные методы для ее выполнения.

SQL Server

Для конкатенации строк с помощью T-SQL в SQL Server имеется два основных метода, первый использует для конкатенации оператор +. Для примера соединим в один столбец имя и фамилию наших клиентов:

Делается легко с добавлением пробела посередине.

Тот же результат можно получить с помощью функции CONCAT, используя запятую как разделитель между параметрами функции:

Oracle

В Oracle оператором конкатенации является двойной символ «|», т.е. ||:

В Oracle функция CONCAT может использоваться только с двумя строками, поэтому следующий оператор не работает:

Будет возвращена ошибка:

PostgreSQL

В PostgreSQL оператор конкатенации такой же, как и в Oracle:

А функция CONCAT работает в PostgreSQL, как в SQL Server:

Функции SQL для получения подстроки

Другой типичной операцией является извлечение некоторой части строки. Например, представим, что нам нужно извлечь инициал имени (Firstname) и дополнить его точкой с последующей фамилией (Lastname) для каждого заказчика.

SQL Server

В SQL Server мы используем функцию SUBSTRING:

Функция SUBSTRING принимает в качестве первого параметра строку, второго — начальную позицию подстроки и третьего — количество символов, которые нужно извлечь.

Тот же результат можно получить с помощью функции LEFT:

Функция LEFT извлекает указанное число символов от начала строки (слева).

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

Oracle

В Oracle эта функция похожа, хотя несколько отличается ее имя — SUBSTR:

Остальное аналогично SQL Server.

К сожалению, в Oracle PL/SQL нет функций LEFT или RIGHT, поэтому мы будем всегда использовать SUBSTR для извлечения подстроки.

PostgreSQL

В PostgreSQL, как и в SQL Server, есть функция SUBSTRING:

Мы также можем использовать функцию LEFT:

и использовать функцию RIGHT:

Удаление начальных и концевых пробелов в строке

Подобно функциям LEFT и RIGHT, имеются функции LTRIM and RTRIM, которые используются для удаления пробелов слева или справа заданной строки. Они часто используются для очистки данных, с чем я часто сталкивался при работе с ETL в хранилищах данных.

SQL Server

Сначала мы испортим данные добавлением некоторого числа пробелов в начале некоторых строк столбца FirstName:

Теперь посмотрим, как выглядят данные:

Как ожидалось, в начале каждой строки появились пробелы.

Теперь почистим данные:

И снова проверим:

Мы можем сделать то же самое с RTRIM для удаления пробелов с правой стороны строки.

В SQL Server 2017 и выше мы можем удалить пробелы слева и справа одновременно с помощью функции TRIM.

Давайте снова испортим данные, и на этот раз добавим пробелы ко всем строкам таблицы:

Посмотрим на наши данные:

Почистим их с помощью TRIM:

Вот что получилось:

Oracle

В Oracle мы имеем в точности те же самые функции. Давайте сначала испортим наш данные:

Заметьте, что поскольку у нас нет функции RIGHT в Oracle, мы должны использовать слегка обходной путь с привлечением функции LENGTH, которая возвращает значение длины в символах строки. Такая же функция также существует в SQL Server и PostgreSQL, хотя в SQL Server она называется LEN. Тогда, используя это число в качестве начальной точки для функции SUBSTR, мы можем вернуть последний символ строки.

Посмотрим на данные:

Теперь почистим их с помощью функции LTRIM:

Снова проверим данные:

Теперь давайте испортим данные для функции TRIM:

Получим такие данные:

И снова почистим их:

Данные снова в порядке:

Заметим, что в Oracle есть отличие в использовании функций TRIM, LTRIM и RTRIM, которые могут применяться для удаления других заданных символов, а не только пробелов, как в SQL Server. Давайте сделаем пример, добавив некоторое символы в начале столбца с именем:

update chinook.Customer
set FirstName=’. ‘||FirstName
where substr(FirstName,length(FirstName),1) in (‘a’,’e’,’i’,’o’,’u’);
commit;

А теперь почистим их с помощью функции LTRIM:

Наши данные вернулись к предыдущему состоянию:

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

PostgreSQL

В PostgreSQL мы имеем те же самые функции, с тем же синтаскисом и смыслом.

Начнем с порчи данных:

Посмотрим, что у нас находится в таблице:

LTRIM и RTRIM работают точно так же, но здесь мы имеем функциональность, подобную Oracle, удаления не только пробелов, что принимается по умолчанию. Протестируем на тех же тестах, которые мы использовали в случае с Oracle:

Теперь почистим данные:

Замена текста в строке

Другой полезной функцией манипуляции строками является REPLACE, которая, как предполагает имя, используется для замены заданной строки другой строкой.

Перейдем к примеру: представим, что нам нужно заменить в столбце Company слово «Inc.» на «Co.».

SQL Server

Эту задачу легко решить с использованием функции REPLACE. Сначала посмотрим на данные:

Теперь обновим их:

Мы можем увидеть изменения:

Oracle

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

А теперь REPLACE:

PostgreSQL

Обатите внимание, что мне потребовалось добавить ORDER BY, чтобы получить заказчиков в том же порядке, что и в других двух РСУБД.