Как найти длину строки sql

Здравствуйте, уважаемые читатели блога 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 есть сразу несколько функций для конкатенации строк.

Функция CONCAT:

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 есть три функции.

Функция LTRIM:

string LTRIM(str string)

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

Функция RTRIM:

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 ( ‘замена подстроки’, ‘подстроки’, ‘текста’ )
Результат: ‘замена текста’

Функция REPEAT:

string REPEAT(str string, count integer)

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

Пример:

SELECT REPEAT (‘w’, 3);
Результат: ‘www’

Функция 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. Поэтому не забудьте подписаться на обновления блога. До новых встреч!

У SQL сервера достаточно много мощных функций для работы со строками и в этом разделе мы рассмотрим наиболее интересные и часто используемые из них. Из моего личного опыта (ваши задачи могут дать другой результат), наиболее часто используемой является функция SUBSTRING. Именно с нее мы и начнем.

SUBSTRING

Помниться, что мы добавили к значениям в колонке имен работников префикс ‘mr.’ (см. разд. 2.17). А как теперь от него избавится во время обращения к таблице? Достаточно просто, если воспользоваться функцией SUBSTRING, которая возвращает указанную часть строки. Этой функции необходимо передать три параметра:

1. Поле, часть строки которого нужно получить;
2. Первый символ;
3. Количество интересующих нас символов.

Посмотрим, как вышесказанное можно реализовать в виде запроса:

SELECT idPeoples,
      CASE SUBSTRING(vcFamil, 1, 3)
        WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255)
        ELSE vcFamil
       END
FROM tbPeoples

В этом примере, мы выбираем только два поля: «idPeoples» и поле, результат которого зависит от проверки CASE. В данном случае CASE проверяет результат работы функции SUBSTRING, которая выбирает символы из поля «vcFamil» начиная с первого по третий. Если результат равен ‘mr.’, то необходимо обрезать этот префикс.

Для того, чтобы отбросить ненужные символы от значения поля, мы снова пользуемся функцией SUBSTRING, но теперь выбираем символы, начиная с четвертного (начиная с первого, после ‘mr.’). В качестве количества символов я указал число 255, что больше максимального значения поля, а значит, строка будет выбрана до конца, начиная 4-го.

Теперь попробуем обновить данные в таблице, чтобы в поле «vcName», чтобы в нем не было лишних символов ‘mr.’. Для этого выполняем следующий запрос:

UPDATE tbPeoples
SET vcFamil=(case SUBSTRING(vcFamil, 1, 3)
      WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255)
      ELSE vcFamil
     END)

В этом примере полю «vcName»присваивается результат сравнения CASE, который мы уже рассмотрели выше. Таким образом, мы избавились от лишних букв в фамилиях.

LEFT

Задачу обрезание лишних символов из начала строки можно было бы решить и с использованием функции LEFT, которая возвращает указанное количество символов, начиная с 1-го. Функции нужно передать следующие два параметра:

1. Поле, подстроку которого нужно получить;
2. Количество символов.

Следующий пример формирует ФИО, в котором имя и отчество сокращены:

SELECT vcFamil+' '+left(vcName, 1)+'. '+left(vcSurName, 1)+'.'
FROM tbPeoples

Поле «vcFamil» выводится полностью, а вот от имени и отчества выводится только один левый (первый) символ.

Теперь посмотрим, как можно было использовать LEFT для обрезания префикса ‘mr.’:

UPDATE tbPeoples
SET vcFamil=(case LEFT(vcFamil, 3)
      WHEN 'mr.' THEN SUBSTRING(vcFamil, 4, 255)
      ELSE vcFamil
     END)

LEN

Функция LEN позволяет определить длину строки или значения поля. Функции достаточно передать строку или имя поля, длина значений которого нас интересует. Например, следующий запрос отобразить длину всех значений в поле «vcFamil»:

SELECT vcFamil, len(vcFamil)
FROM tbPeoples

В следующем примере мы ищем записи, в которых фамилия состоит 7-и символов:

SELECT vcFamil
FROM tbPeoples
WHERE len(vcFamil)=7

LOWER

Если ваш сервер настроен так, что строки чувствительные к регистру букв, то с поиском по строковым полям могут быть серьезные проблемы. Если вы указали фамилию как Иванов, то это значение не будет равно ИВАНОВ, а значит, мы не увидим необходимую запись. Проблему решает функция LOWER, которая приводит указанную строку к нижнему регистру.

Рассмотрим пример. В следующем запросе мы выбираем все фамилии, при этом они отображаются в нижнем регистре (маленькими буквами):

SELECT LOWER(vcFamil)
FROM tbPeoples

Теперь посмотрим на следующий пример:

SELECT *
FROM tbPeoples
WHERE LOWER(vcFamil)=LOWER('Сидоров')

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

UPPER

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

SELECT *
FROM tbPeoples
WHERE UPPER(vcFamil)=UPPER('Сидоров')

Если вам нужно сравнить две строки не обращая внимания на используемых регистр букв внутри строк, можно использовать как UPPER, так и LOWER. Разницы никакой нет, поэтому выбирайте то, что больше нравится.

LTRIM и RTRIM

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

Иванов
 Иванов

Когда смотришь на эти фамилии, то видно, что вторая строка сдвинута вправо за счет пробела вначале. Это значит, что база данных будет воспринимать эти значения по-разному. Чтобы избавится от лишних пробелов, как раз используют функции LTRIM и RTRIM. Например:

SELECT *
FROM tbPeoples
WHERE LTRIM(vcFamil)=LTRIM(' Сидоров')

В этом примере поле «vcFamil» сравнивается с фамилией Сидоров, с пробелом в начале. Чтобы убрать пробел используется функция LTRIM. В следующем примере мы убираем и левые и правые пробелы:

-- Убрать лишние пробелы
SELECT *
FROM tbPeoples
WHERE vcFamil=LTRIM(RTRIM(' Сидоров '))

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

SELECT *
FROM tbPeoples
WHERE vcFamil='Сидоров '

Если работник с фамилией Сидоров (без пробелов в конце) существует в таблице, и запрос отобразил его, то сервер автоматически убрал пробел.

PATINDEX

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

SELECT vcFamil, PATINDEX('%О_О%', vcFamil)
FROM tbPeoples

Если посмотреть на функцию, то пока не понятно, чем она отличается от LIKE с шаблоном? Все очень просто – LIKE используется для создания ограничений в секции WHERE, а PATINDEX возвращает индекс символа, начиная с которого идет указанный шаблон в строке. Если бы мы использовали LIKE, то сервер вернул бы нам только те строки, где найден шаблон:

SELECT vcFamil
FROM tbPeoples
WHERE vcFamil LIKE '%О_О%'

Если использовать функцию PATINDEX, то в результат попадут все строки (мы не ограничиваем вывод в секции WHERE), но там где в фамилии нет шаблона, в соответствующей строке будет стоять ноль, а там где есть, будет стоять 1. Посмотрим на пример результата выполнения запроса с использованием функции PATINDEX:

vcFamil                             Ind         
----------------------------------------------- 
ПОЧЕЧКИН                            0
ПЕТРОВ                              0
СИДОРОВ                             4
КОНОНОВ                             2
СЕРГЕЕВ                             0

В данном примере шаблон ‘%О_О%’ присутствует в фамилии Сидоров. Начиная с четвертого символа идут буквы «оро».

REPLACE

Функция replace позволяет найти в значении поля подстроку и заменить ее на новое значение. У этой функции три параметра:

1. Строка, в которой нужно искать подстроку;
2. Подстрока, которую ищем;
3. Значение, которое нужно подставить.

Посмотрим пример использования этой функции:

SELECT vcFamil, REPLACE(vcFamil, 'оро', 'аро') AS Ind
FROM tbPeoples
WHERE PATINDEX('%О_О%', vcFamil)>0

Мы выбираем из таблицы два поля: фамилию и результат функции REPLACE. Функция ищет в поле «vcFamil» строку «оро» и заменяет ее на строку «аро». Чтобы лучше было понятно, посмотрим на результат работы функции:

vcFamil                      Ind                                                                                                                                                                                                                                                              
----------------------------------------------
СИДОРОВ                      СИДароВ
КОНОНОВ                      КОНОНОВ
КОРОВА                       КароВА
МОЛОТКОВ                     МОЛОТКОВ
САДОВОДОВ                    САДОВОДОВ
СОДОРОЧКИН                   СОДароЧКИН

(6 row(s) affected)

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

REPLICATE

С помощью функции REPLICATE можно размножать строку. У функции два параметра:

1. Строка или имя поля, которое нужно вывести несколько раз;
2. Количество необходимых повторений

Переходим к примеру. Следующий запрос выводит в результирующий набор дважды значение поля фамилии:

SELECT REPLICATE(vcFamil, 2) 
FROM tbPeoples

В результате мы увидим нечто подобное:

ПОЧЕЧКИНПОЧЕЧКИН
ПЕТРОВПЕТРОВ
СИДОРОВСИДОРОВ
КОНОНОВКОНОНОВ
СЕРГЕЕВСЕРГЕЕВ
ВАСИЛЬЕВВАСИЛЬЕВ
...

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

SELECT REPLICATE('=', 50)

Результат:

==================================================

Красиво? А главное удобно в управлении.

REVERSE

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

SELECT REVERSE(vcFamil) 
FROM tbPeoples

В реальных приложениях полностью строку вы будете менять достаточно редко, а вот часть строки может меняться. Например, в следующем запросе в фамилии меняются местами первые два символа:

SELECT REPLACE(vcFamil, 
               LEFT(vcFamil, 2), 
               REVERSE(LEFT(vcFamil, 2))
               )
FROM tbPeoples

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

1. Название поля, где хранится фамилия;
2. Первые два символа. Для получения первых двух символов используем уже знакомую нам функцию LEFT;
3. В качестве строки, которая должна будет поставлена вместо первых двух символов фамилии, выступают те же два символа, только перевернутые.

SPACE

С помощью функции SPACE можно создавать пробелы. В качестве единственного параметра нужно указать число, которое определяет количество возвращаемых пробелов. Работа функции идентична REPLICATE, если в качестве клонируемого символа указать пробел.

Допустим, что нам нужно вывести на экран поля фамилию и имя, разделенные 5-ю пробелами. Можно сделать так:

SELECT vcFamil+'     '+vcName
FROM tbPeoples

А можно воспользоваться функцией SPACE:

SELECT vcFamil+SPACE(5)+vcName
FROM tbPeoples

Зачем нужна функция, когда можно воспользоваться без нее? Допустим, что вам нужно использовать 5 пробелов в нескольких местах большого сценария. Все легко решается без функций, но в последствии оказалось, что количество пробелов должно быть не 5, а 10. Придется пересматривать весь сценарий и корректировать пробелы. А если бы мы использовали SPACE в сочетании с переменными, то проблема решилась бы намного проще.

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

DECLARE @sp int
SET @sp=10

SELECT vcFamil+SPACE(@sp)+vcName+SPACE(@sp)+vcSurName
FROM tbPeoples

Теперь, достаточно только изменить значение переменной, и количество пробелов изменено во всем сценарии. А главное – что количество пробелов может быть определено динамически, на основе запросов к таблице.

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.

STUFF

Функция STUFF позволяет вставить строку в определенную позицию другой строки. У этой функции четыре параметра:

1. Строка, которую нужно изменить;
2. Позиция, в которую должна произойти вставка;
3. Количество удаляемых символов;
4. Вставляемая строка.

Длина вставляемой строки не обязательно должна быть равна значению из 3-го параметра. Во время выполнения, функция сначала удаляет определенное количество символов, начиная с позиции из второго параметра, а затем вставляет новую строку.

Рассмотрим пример, в котором цена вставляется в поле названия товара, начиная с первой позиции, не удаляя ни одного из символов:

SELECT STUFF([Название товара], 1, 0, STR(Цена, 10, 2)+' ')
FROM Товары

Результат работы функции будет следующим:

--------------------------------------------
     13.60 КАРТОФЕЛЬ
     23.00 Сок
     25.00 Шоколад
      6.00 Хлеб
     18.40 Сок
     12.00 Молоко
      6.00 Хлеб
...

На этом примере более наглядно видно, что вставляемая цена выравнивается вправо. Так как мы указали в функции STR количество символов равное 10, то вставляется не реальный размер цены, а именно 10 символов.

Попробуйте увеличить третий параметр до 1. В этом случае, первый символ в названии товара будет удален, а вместо него будет вставлена цена.

In this article, we’ll look at the LENGTH function and its variants, and the VSIZE function, explain how they work, and see some examples.

Purpose of the SQL LENGTH or LEN Function

The SQL LENGTH or LEN function will find the length of, or the number of characters in, a string value.

You specify the string you want to find the length of, and the LENGTH or LEN function returns a number representing the number of characters of that string.

The function is called LENGTH in Oracle, MySQL, and Postgres, and it’s called LEN in SQL Server.

Oracle has several variants of the LENGTH function, which are the same as the basic LENGTH function – to find the length of a specified string.

However, the difference between these functions is that they are useful when working with special character sets.

The differences are:

• LENGTH: Returns the length of the string
• LENGTH2: Returns the length of the string in UCS2 code points
• LENGTH4: Returns the length of the string in UCS4 code points
• LENGTHB: Returns the length of the string in bytes
• LENGTHC: Returns the length of the string in characters

Purpose of the VSIZE Function

The VSIZE function returns the number of bytes of an expression using the internal representation.

That sounds a lot like the LENGTH function, doesn’t it?

I’ll explain the differences shortly after we cover the syntax and parameters.

SQL LENGTH/LEN Function Syntax and Parameters

The syntax of the SQL LENGTH function is:

LENGTH ( string_value )

The SQL LEN function is the same:

LEN ( string_value )

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:

LENGTH2 ( string )
LENGTH4 ( string )
LENGTHB ( string )
LENGTHC ( string )

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 SQL LENGTH function:

• 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.

Oracle VSIZE Function Syntax and Parameters

The syntax of the VSIZE function is:

VSIZE ( expression )

The parameters of the VSIZE function are:

• expression (mandatory): The expression to calculate the VSIZE for.

If the expression value is NULL, then the function returns NULL.

What’s the Difference Between LENGTH and VSIZE Functions in Oracle?

If you think that the VSIZE function looks a lot like the LENGTH function, you’re right. They are pretty similar.

But, they have some differences.

• LENGTH returns the number of characters in the specified string, but VSIZE finds the number of bytes that a string uses. One byte does not always equal one character (related: character sets in Oracle).
• LENGTH takes any character argument, but VSIZE takes almost any data type, so LENGTH will have to do an implicit conversion if another data type is specified (such as a number).

So looking at VSIZE vs LENGTH in Oracle will depend on your requirements.

The examples section below looks at an example of both functions.

Examples of the LENGTH Functions

Here are some examples of the SQL LENGTH or SQL LEN function.

Example 1

This example shows the LENGTH function using a string value.

SELECT LENGTH('Software Developer');

Or, using LEN:

SELECT LEN('Software Developer');

Result:

18

The length of the string “Software Developer” is shown as 18 characters.

Example 2

This example shows the length of a string value. However, we’re using a CHAR value as it has spaces at the end.

SELECT
LENGTH('Software Developer     ');

Result:

23

The length of this string is slightly longer than the last example due to the spaces.

Example 3

This example uses LENGTH with a NULL value. We’ve run the query on the table to see different results.

SELECT
country,
LENGTH(country) AS COUNTRY_LENGTH
FROM customers;

Or, using the LEN function:

SELECT
country,
LEN(country) AS COUNTRY_LENGTH
FROM customers;

Result:

COUNTRY	COUNTRY_LENGTH
USA	3
USA	3
Canada	6
UK	2
USA	3
(null)	(null)
France	6
(null)	(null)

You can see that the length of each country’s value is shown. Where the country is NULL, a NULL value is returned by LENGTH.

Example 4

This example shows what happens when we run LENGTH on an empty string or a string of spaces.

SELECT
LENGTH('') as LENGTH_EMPTY,
LENGTH(' ') as LENGTH_SPACE
FROM DUAL;

Or, using LEN:

SELECT
LEN('') as LENGTH_EMPTY,
LEN(' ') as LENGTH_SPACE
FROM DUAL;

Result:

LENGTH_EMPTY	LENGTH_SPACE
(null)	1

As you can see, the length of an empty string is NULL, but the length of a single space is 1.

Example 5

This example uses LENGTH on a numeric value.

SELECT LENGTH(10973);

Or, using LEN:

SELECT LEN(10973);

Result:

5

The LENGTH and LEN function still works on numeric values.

Example 6 – LENGTH Function Variants

This example shows how each of the LENGTH functions treats a specific string.

SELECT
'Database Star table column row' AS sample,
LENGTHB('Database Star table column row') AS lengthb_test,
LENGTHC('Database Star table column row') AS lengthc_test,
LENGTH2('Database Star table column row') AS length2_test,
LENGTH4('Database Star table column row') AS length4_test
FROM dual;

Result:

SAMPLE	LENGTHB_TEST	LENGTHC_TEST	LENGTH2_TEST	LENGTH4_TEST
Database Star table column row	30	30	30	30

Examples of the VSIZE Function

Here are some examples of the VSIZE function.

Example 1 – Basic VSIZE

This example is a basic example of the VSIZE function.

SELECT
VSIZE('DatabaseStar')
FROM dual;

Result:

12

The result is 12 because 12 bytes would be used.

Example 2 – VSIZE vs LENGTH

Let’s see how VSIZE and LENGTH compare using an example.

First, let’s set up a new table with some data.

CREATE TABLE vsize_test (
  single_byte_char VARCHAR2(10),
  multi_byte_char NVARCHAR2(10)
);

INSERT INTO vsize_test (single_byte_char, multi_byte_char)
VALUES ('Database', 'Databӑse');

Now, let’s select these values.

SELECT single_byte_char, multi_byte_char
FROM vsize_test;

Result:

SINGLE_BYTE_CHAR	MULTI_BYTE_CHAR
Database	Datab?se

Now, let’s use the VSIZE and LENGTH functions on these values.

SELECT
single_byte_char,
VSIZE(single_byte_char) AS vsize_single,
LENGTH(single_byte_char) AS length_single,
multi_byte_char,
VSIZE(multi_byte_char) AS vsize_multi,
LENGTH(multi_byte_char) AS length_multi
FROM vsize_test;

Result:

SINGLE_BYTE_ CHAR	VSIZE_ SINGLE	LENGTH_ SINGLE	MULTI_BYTE_ CHAR	VSIZE_ MULTI	LENGTH_ MULTI
Database	8	8	Datab?se	16	8

You can see that the results are different here.

Similar Functions

Some functions which are similar to these functions are:

• VSIZE: Finds the size of a string, similar to Length.
• INSTR2/INSTR4/INSTRB/INSTRC: The variations of the INSTR function which find a smaller string inside a larger string.

If you want to know more about SQL functions, you can find a full list of Oracle SQL functions here.