Функции Transact-SQL для работы со строками и данными типа даты/времени

Функции работы со строками в MS SQL SERVER

Функция LEN(строковое выражение) возвращает число символов в строке, задаваемой строковым выражением. Ограничимся случаем, когда число символов больше единицы:

Консоль

Выполнить

SELECT * 
FROM (SELECT class +' '+ name AS cn 
 FROM Ships
 UNION ALL 
 SELECT 'a' AS nc
 ) x
WHERE LEFT(cn, 1) = RIGHT(cn, 1) AND 
 LEN(cn) > 1;

SELECT * FROM (SELECT class +' '+ name AS cn FROM Ships UNION ALL SELECT 'a' AS nc ) x WHERE LEFT(cn, 1) = RIGHT(cn, 1) AND LEN(cn) > 1;

    Схема

Реализация функции LEN() в MS  Cистема управления реляционными базами данных (СУБД), разработанная корпорацией Microsoft. Язык структурированных запросов) — универсальный компьютерный язык, применяемый для создания, модификации и управления данными в реляционных базах данных. SQL Server имеет одну особенность, а именно, она не учитывает концевые пробелы.

Давайте выполним следующий код:

DECLARE @chr AS CHAR(12), @vchr AS VARCHAR(12);
SELECT @chr = 'abcde' + REPLICATE(' ', 5), @vchr = 'abcde'+REPLICATE(' ', 5);
SELECT LEN(@chr), LEN(@vchr);
SELECT DATALENGTH(@chr), DATALENGTH(@vchr);

Функция REPLICATE добавляет справа к константе 'abcde' пять пробелов, которые не учитываются функцией LEN, - в обоих случаях мы получаем 5.

Функция DATALENGTH возвращает число байтов в строковом представлении и демонстрирует различие в типах данных CHAR и VARCHAR. DATALENGTH возвращает 12 для типа CHAR(12), и 10 - для VARCHAR(12).

Как и следовало ожидать, DATALENGTH для переменной типа VARCHAR вернуло фактическую длину строковой переменной. Но почему для переменной типа CHAR результат оказался равным 12? Дело в том, что CHAR является типом данных фиксированной длины. Если значение переменной меньше объявленной длины, а мы использовали CHAR(12), значение переменной будет дополнено концевыми пробелами, чтобы "выровнять" длину до 12 символов.

На сайте имеются задачи, в которых требуется упорядочить (найти максимум и т. д.) в числовом порядке значения, представленные в текстовом формате. Например, номер места в самолете (2d) или скорость привода CD-ROM (24x). Проблема заключается в том, что текст сортируется так (по возрастанию):

Действительно,

Консоль

Выполнить

SELECT '1a' AS place
UNION ALL 
SELECT '2a'
UNION ALL 
SELECT '11a'
ORDER BY 1;

SELECT '1a' AS place UNION ALL SELECT '2a' UNION ALL SELECT '11a' ORDER BY 1;

    Схема

Нет схемы

Если же требуется упорядочить места в самолете в порядке следования рядов, то порядок должен быть таким:

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

1. Извлечь число из строки.
2. Привести его к числовому формату.
3. Выполнить сортировку по приведенному значению.

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

LEFT(place, LEN(place) - 1)

Если только этим и ограничиться, то получим

Приведение к числовому формату может быть следующим:

CAST (LEFT(place, LEN(place) - 1) AS INT)

Осталось выполнить сортировку:

Консоль

Выполнить

SELECT * 
FROM (SELECT '1a' AS place
 UNION ALL 
 SELECT '2a'
 UNION ALL 
 SELECT '11a'
 ) x
ORDER BY CAST(LEFT(place, LEN(place) - 1) AS INT);

SELECT * FROM (SELECT '1a' AS place UNION ALL SELECT '2a' UNION ALL SELECT '11a' ) x ORDER BY CAST(LEFT(place, LEN(place) - 1) AS INT);

    Схема

Нет схемы

Что и требовалось доказать.

Ранее мы для извлечения числа из текстовой строки пользовались функцией LEFT, так как нам было известно априори, какое число символов нужно убрать справа (один). А если же нужно извлечь строку из подстроки не по известной позиции символа, а по самому символу? Например: извлечь все символы до первой буквы х (значение скорости привода CD-ROM).

В этом случае мы можем использовать также уже рассмотренную ранее функцию CHARINDEX, которая позволит определить неизвестную позицию символа:

Консоль

Выполнить

SELECT model, LEFT(cd, CHARINDEX('x', cd) -1) 
FROM PC;

SELECT model, LEFT(cd, CHARINDEX('x', cd) -1) FROM PC;

    Схема

Содержание:

Функция LEN(), концевые пробелы и уникальность