Substring (transact-sql)substring (transact-sql)

STR

С помощью функции STR можно форматировать дробные числа в строку. Чем это отличается от преобразования типов? Тип остается тем же, а на экран мы выводим строку в нужном виде. Функции нужно передать три параметра:

1. Дробное число, которое нужно форматировать;
2. Общее количество символов, включая числа до и после запятой, пробелы и знак;
3. Количество знаков после запятой.

Допустим, что нам нужно вывести название и цену товара. Но цена имеет тип money, который содержит слишком большое количество нулей. Чтобы избавиться от лишних чисел после запятой и получить строку, можно сначала привести тип money к типу number(10, 2), а потом результат привести к строке. Но можно решить все одной командой форматирования STR:

SELECT , STR(Цена, 10, 2)
FROM Товары

Выполните этот запрос и обратите внимание, что второе поле (отформатированная цена) выровнена вправо:

Название товара                                               
-------------------------------------------------- ---------- 
КАРТОФЕЛЬ                                               13.60
Сок                                                     23.00
Шоколад                                                 25.00
Хлеб                                                     6.00
Сок                                                     18.40
...

Выравнивание происходит из-за второго параметра – числа 10. Мы задали общее число символов, и выравнивание будет происходить по правой позиции указанного значения. Если второй параметр равен 10, а число состоит из 4 символов, то в начало результирующей строки будет добавлено 6 пробелов. Учитывайте это, при использовании функции STR.

Example — Match on Words

Let’s start by extracting the first word from a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)')
FROM dual;

Result: 'TechOnTheNet '

This example will return ‘TechOnTheNet ‘ because it will extract all non-whitespace characters as specified by and then the first whitespace character as specified by . The result will include both the first word as well as the space after the word.

If you didn’t want to include the space in the result, we could modify our example as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)')
FROM dual;

Result: 'TechOnTheNet'

This example would return ‘TechOnTheNet’ with no space at the end.

If we wanted to find the second word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 2)
FROM dual;

Result: 'is '

This example would return ‘is ‘ with a space at the end of the string.

If we wanted to find the third word in the string, we could modify our function as follows:

SELECT REGEXP_SUBSTR ('TechOnTheNet is a great resource', '(\S*)(\s)', 1, 3)
FROM dual;

Result: 'a '

Oracle LENGTH Function Syntax and Parameters

The syntax of the Oracle LENGTH function is:

It returns a numeric value that represents the length of the supplied string.

The syntax of the LENGTH2, LENGTH4, LENGTHB, and LENGTHC functions are all the same:

The parameters of the LENGTH function and its variants are:

string_value (mandatory): This is the string value to check the length of.

Some points to remember about the Oracle LENGTH function and its variants:

• If string_value is NULL, then LENGTH will return NULL.
• If string_value is an empty string, the LENGTH will return NULL.
• The string_value can be any of the character data types – CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, NCLOB.
• If the string_value is a CHAR data type, then the LENGTH will include any trailing spaces in the value.

Функция REPLACE

REPLACE ( <строка1> , <строка2> , <строка3> )

Заменяет в строке1 все вхождения строки2 на строку3. Эта функция, безусловно, полезна в операторах обновления (UPDATE), если нужно изменить (исправить) содержимое столбца. Пусть, например, нужно заменить все пробелы дефисом в названиях кораблей. Тогда можно написать

(Этот пример можно выполнить на странице с упражнениями DML, где разрешаются запросы на изменение данных)

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

А если нам нужно определить число вхождений произвольной последовательности символов, скажем, передаваемой в качестве параметра в хранимую процедуру? Использованный выше алгоритм в этом случае следует дополнить делением на число символов в искомой последовательности:

Для удвоения числа искомых символов здесь применялась конкатенация — @str + @str . Однако для этой цели можно использовать еще одну функцию — REPLICATE, которая повторяет первый аргумент такое число раз, которое задается вторым аргументом.

Т.е. мы повторяем дважды подстроку, хранящуюся в переменной @str .

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

STUFF (<строка1> , <стартовая позиция> , <L> , <строка2>)

Эта функция заменяет подстроку длиной L, которая начинается со стартовой позиции в строке1, на строку2.

Пример. Изменить имя корабля: оставив в его имени 5 первых символов, дописать «_» (нижнее подчеркивание) и год спуска на воду. Если в имени менее 5 символов, дополнить его пробелами.

Можно решать эту задачу с помощью разных функций. Мы же попытаемся это сделать с помощью функции STUFF. В первом приближении напишем (ограничимся запросом на выборку):

Третьим аргументом (количество символов для замены) я использую LEN(name), т.к. мне нужно заменить все символы до конца строки, поэтому я беру с запасом — исходное число символов в имени. И все же этот запрос вернет ошибку. Причем дело не в третьем аргументе, а в четвертом, где выполняется конкатенация строковой константы и числового столбца. Ошибка приведения типа. Для преобразования числа к его строковому представлению можно воспользоваться еще одной встроенной функцией — STR:

STR ( <число с плавающей точкой> [ , <длина> [ , <число десятичных знаков> ] ] )

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

Обратите внимание, что если полученное строковое представление числа меньше заданной длины, то добавляются лидирующие пробелы. Если же результат больше заданной длины, то усекается дробная часть (с округлением); в случае же целого числа получаем соответствующее число звездочек «*»:

Кстати, по умолчанию используется длина в 10 символов. Имея в виду, что год представлен четырьмя цифрами, напишем

Уже почти все правильно. Осталось учесть случай, когда число символов в имени менее 6, т.к. в этом случае функция STUFF дает NULL. Ну что ж вытерпим до конца мучения, связанные с использованием этой функции в данном примере, попутно применив еще одну строковую функцию.
Добавим конечные пробелы, чтобы длина имени была заведомо больше 6. Для этого имеется специальная функция SPACE

SPACE(<число пробелов>):

Example — Using _ wildcard (underscore wildcard)

Next, let’s explain how the _ wildcard (underscore wildcard) works in the Oracle LIKE condition. Remember that _ wildcard is looking for only one character.

For example:

SELECT supplier_name
FROM suppliers
WHERE supplier_name LIKE 'Sm_th';

This Oracle LIKE condition example would return all suppliers whose supplier_name is 5 characters long, where the first two characters is ‘Sm’ and the last two characters is ‘th’. For example, it could return suppliers whose supplier_name is ‘Smith’, ‘Smyth’, ‘Smath’, ‘Smeth’, etc.

Here is another example:

SELECT *
FROM suppliers
WHERE account_number LIKE '92314_';

You might find that you are looking for an account number, but you only have 5 of the 6 digits. The example above, would retrieve potentially 10 records back (where the missing value could equal anything from 0 to 9). For example, it could return suppliers whose account numbers are:

Вопросы и ответы

Вопрос:

Почему не получается отсортировать дни недели по порядку?

Oracle PL/SQL

SELECT ename,
hiredate,
TO_CHAR((hiredate),’fmDay’) «Day»
FROM emp
ORDER BY «Day»;

Ответ:

В приведенном выше запросе SQL, маска формата ‘fmDay’ в функции TO_CHAR вернет наименование дня, а не числовое значение дня.

Для сортировки дней недели, вам нужно вернуть числовое значение дня с помощью маски формата ‘fmD’ следующим образом:

Oracle PL/SQL

SELECT ename,
hiredate,
TO_CHAR((hiredate),’fmD’) «Day»
FROM emp
ORDER BY «Day»;

Функции LTRIM и RTRIM

LTRIM (<строковое выражение>)

RTRIM (<строковое выражение>)

отсекают соответственно лидирующие и конечные пробелы строкового выражения, которое неявно приводится к типу VARCHAR.

Пусть требуется построить такую строку: имя пассажира_идентификатор пассажира для каждой записи из таблицы Passenger. Если мы напишем

то в результате получим что-то типа:

A                               _1

Это связано с тем, что столбец name имеет тип CHAR(30). Для этого типа короткая строка дополняется пробелами до заданного размера (у нас 30 символов). Здесь нам как раз и поможет функция RTRIM:

Example — Match on nth_occurrence

The next example that we will look at involves the nth_occurrence parameter. The nth_occurrence parameter allows you to select which occurrence of the pattern you wish to extract the substring for.

First Occurrence

Let’s look at how to extract the first occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 1, 'i')
FROM dual;

Result: 'e'

This example will return ‘e’ because it is extracting the first occurrence of a vowel (a, e, i, o, or u) in the string.

Second Occurrence

Next, we will extract for the second occurrence of a pattern in a string.

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 2, 'i')
FROM dual;

Result: 'O'

This example will return ‘O’ because it is extracting the second occurrence of a vowel (a, e, i, o, or u) in the string.

Third Occurrence

For example:

SELECT REGEXP_SUBSTR ('TechOnTheNet', 'a|e|i|o|u', 1, 3, 'i')
FROM dual;

Result: 'e'

This example will return ‘e’ because it is extracting the third occurrence of a vowel (a, e, i, o, or u) in the string.

Example — Using EXISTS Clause

You can also perform more complicated deletes.

You may wish to delete records in one table based on values in another table. Since you can’t list more than one table in the Oracle FROM clause when you are performing a delete, you can use the Oracle EXISTS clause.

For example:

DELETE FROM suppliers
WHERE EXISTS
  ( SELECT customers.customer_name
    FROM customers
    WHERE customers.customer_id = suppliers.supplier_id
    AND customer_id > 25 );

This Oracle DELETE example would delete all records in the suppliers table where there is a record in the customers table whose customer_id is greater than 25, and the customer_id matches the supplier_id.

If you wish to determine the number of rows that will be deleted, you can run the following Oracle SELECT statement before performing the delete.

Функция LTRIM

Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.

  
  SELECT LTRIM (city, 'г.') AS gorod FROM table

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