UPPER (Transact-SQL) UPPER (Transact-SQL)
Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) База данных SQL Azure Azure SQL Database База данных SQL Azure Azure SQL Database Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Параллельное хранилище данных Parallel Data Warehouse Параллельное хранилище данных Parallel Data Warehouse Применимо к: Applies to: SQL Server SQL Server (все поддерживаемые версии) SQL Server SQL Server (all supported versions) База данных SQL Azure Azure SQL Database База данных SQL Azure Azure SQL Database Управляемый экземпляр SQL Azure Azure SQL Managed Instance Управляемый экземпляр SQL Azure Azure SQL Managed Instance Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Azure Synapse Analytics Параллельное хранилище данных Parallel Data Warehouse Параллельное хранилище данных Parallel Data Warehouse
Возвращает символьное выражение, в котором символы нижнего регистра преобразованы в символы верхнего регистра. Returns a character expression with lowercase character data converted to uppercase.
Синтаксические обозначения в Transact-SQL Transact-SQL Syntax Conventions
Синтаксис Syntax
Ссылки на описание синтаксиса Transact-SQL для SQL Server 2014 и более ранних версий, см. в статье Документация по предыдущим версиям. To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.
Аргументы Arguments
character_expression character_expression
Выражение символьного типа данных. Is an expression of character data. Аргумент character_expression может быть константой, переменной или столбцом символьных или двоичных данных. character_expression can be a constant, variable, or column of either character or binary data.
Аргумент character_expression должен иметь тип данных, который может быть неявно преобразован в тип varchar. character_expression must be of a data type that is implicitly convertible to varchar. В противном случае используйте CAST для явного преобразования character_expression. Otherwise, use CAST to explicitly convert character_expression.
Типы возвращаемых значений Return Types
varchar или nvarchar varchar or nvarchar
Примеры Examples
В приведенном ниже примере функции UPPER и RTRIM используются для получения из таблицы dbo.DimEmployee фамилий всех людей, при этом все символы фамилии преобразуются в верхний регистр, соответствующая символьная строка обрезается и производится ее сцепление со строкой имени. The following example uses the UPPER and RTRIM functions to return the last name of people in the dbo.DimEmployee table so that it is in uppercase, trimmed, and concatenated with the first name.
Здесь приводится частичный результирующий набор. Here is a partial result set.
Строковые функции SQL – примеры использования
Мы продолжаем изучение языка запросов SQL, и сегодня мы с Вами будем разговаривать о строковых функциях SQL. Мы рассмотрим основные и часто используемые строковые функции, такие как: LOWER, LTRIM, REPLACE и другие, все рассматривать мы будем, конечно же, на примерах.
В прошлой статье «Основы языка запросов SQL – оператор SELECT» мы с Вами узнали, что такое вообще SQL, а также рассмотрели основы оператора SELECT (условия отбора, агрегатные функции), здесь же мы продолжаем изучение оператора SELECT, а если говорить конкретней, то будем рассматривать строковые функции SQL.
Строковые функции используются для возвращения уже отредактированного значения. Другими словами, например, у Вас в колонке все значения состоят более чем из 20 символов, а Вам нужно возвратить всего первые 5 символов, это можно сделать с помощью так называемых строковых функциях.
Теперь перейдем непосредственно к самим функциям. Здесь я рассматриваю только часто используемые в работе или просто полезные строковые функции.
Функция CONCAT
Начнем с функции CONCAT – она используется для объединения значений двух столбцов в один. Допустим у Вас в таблице, «Имя» и «Фамилия» находятся в разных колонках (что логично), а Вам, для какого-нибудь отчета, нужно чтобы они выводились в одной колонке. Вы можете легко использовать данную функцию.
С помощью данного запроса Вы объедините две колонки в одну, т.е. у Вас получится не вот так
| Иван | Иванов |
| Петр | Петров |
| Иван Иванов |
| Петр Петров |
Аналогично этому запросу можно использовать следующую конструкцию (применимо в PostgreSQL).
Или чтобы отделить пробелом введите
т.е. две вертикальные черты объединяют два столбца в один, а чтобы отделить их пробелом я поставил между ними пробел (можно использовать любой символ, например тире или двоеточие) в апострофах и объединил также двумя вертикальными чертами (в Transact-SQL вместо двух вертикальных черточек используется знак +).
Функция INITCAP
Дальше идет также очень полезная функция, INITCAP – которая возвращает значение в строке, в которой каждое слово начинается с заглавной буквы, а продолжается маленькими. Это нужно для того, если у Вас в той или иной колонке не соблюдают правила заполнения и для того чтобы вывести все это дело в красивом виде можно использовать данную функцию, например, у Вас в таблице записи в колонке name следующего вида: ИВАН иванов или петр петров, Вы применяете данную функцию.
И у Вас получится вот так.
| Иван Иванов |
| Петр Петров |
Функция LOWER
Следующая функция также может пригодиться. LOWER – возвращает в значение все слова с маленькой буквы. Используем пример выше, но только будем пользоваться следующий функцией.
А теперь получится вот так.
| иван иванов |
| петр петров |
Функция UPPER
Похожая функция, только возвращает все символы с заглавной буквы, это UPPER.
| ИВАН ИВАНОВ |
| ПЕТР ПЕТРОВ |
Функция LPAD
Далее идет функция LPAD — она пригодится Вам, если Вам необходимо дополнить слева некими символами, до определенного количества знаков. Допустим, Вам необходимо, чтобы длина поля «Имя» была 20 знаков, и если длина имени меньше (например, «Иван» явно меньше 20 знаков), то будет происходить дополнение слева специальным символом (для примера используем тире).
- name – название колонки;
- 20 – количество знаков (длина поля);
- ‘-‘ – символ, которым нужно дополнить до необходимого количества знаков.
Функция RPAD
Сразу рассмотрим обратную функцию. RPAD – действие и синтаксис тот же что и у LPAD, только дополняются символы справа (в LPAD слева).
Функция LTRIM
Далее идет тоже в некоторых случаях полезная функция, LTRIM – эта функция удаляет крайние левые символы, которые Вы укажите. Например, у Вас в базе есть колонка «город», в которой город указан в виде «г.Москва», а также есть города которые указанны в виде просто «Москва». Но Вам нужно вывести отчет только в виде «Москва» без «г.», но как это сделать, если есть и такие и такие? Вы просто указываете своего рода шаблон «г.» и если крайние левые символы начинаются с «г.», то эти символы просто не будут выводиться.
Данная функция просматривает символы слева, если символов по шаблону нет в начале строки, то она возвращает исходное значение ячейки, а если есть, то удаляет их.
Функция RTRIM
Также давайте сразу рассмотрим обратную функцию. RTRIM – то же самое что и LTRIM только символы ищутся справа.
Примечание! В Transact-SQL функции RTRIM и LTRIM удаляют пробелы справа и слева соответственно.
Функция REPLACE
Теперь рассмотрим такую интересную функцию как REPLACE – она возвращает строку, в которой все совпадения символов, заменяются на Ваши символы, которые Вы укажите. Для чего ее можно использовать, например, у Вас в базе есть колонки, в которых встречаются некие разделительные символы, допустим «/». Например, Иван/Иванов, а Вам хотелось бы вывести Иван-Иванов, то напишите
и у Вас произойдет замена символов.
Данная функция заменяет только полное совпадение символов, если например Вы укажите «—» т.е. три тире она и будет искать только три тире, а каждое отдельное тире заменять не будет, в отличие от следующей функции.
Функция TRANSLATE
TRANSLATE – строковая функция, которая заменяет все символы в строке, на те символы, которые Вы укажите. Исходя из названия функции, можно догадаться, что это полный перевод строки. Отличие данной функции от REPLACE в том, что она заменяет каждый символ, который Вы укажите, т.е. у Вас есть три символа, допустим абв и с помощью TRANSLATE Вы его можете заменить на abc таким образом у Вас а=a, б=b, в=c и по такому принципу будут заменяться все совпадения символов. А если Вы заменяли с помощью REPLACE, то у Вас искалось только полное совпадение символов абв расположенных подряд.
Функция SUBSTR
SUBSTR – данная функция, возвращает только тот диапазон символов, который Вы укажите. Другими словами, допустим, строка из 10 символов, а Вам все десять не нужны, а допустим, нужны только 3-8 (с третьего по восьмой). С помощью данной функции Вы легко можете это сделать. Например, у Вас в базе есть какой-нибудь идентификатор, фиксированной длинны (типа: AA-BB-55-66-CC) и каждая комбинация символов что-то означает. И в один прекрасный момент Вам сказали вывести только 2 и 3 комбинацию символов, для этого вы пишите запрос следующего вида.
т.е. мы выводим все символы, начиная с 4 и заканчивая 8, и после этого запроса у Вас выведется вот это:
BB-55
Функция LENGTH – длина строки
Следующая функция также может пригодиться, это LENGTH – которая просто на всего считает количество символов в строке. Например, Вам нужно узнать, сколько символов в каждой ячейки столбца допустим «name», таблица следующего вида.
| Иван |
| Сергей |
| Виталий |
после этого запроса Вы получите вот это.
Заметка! Для комплексного изучения языка SQL рекомендую почитать мою книгу «SQL код», в ней язык SQL рассматривается как стандарт, чтобы после прочтения данной книги можно было работать с языком SQL в любой системе управления базами данных.
Вот мы с Вами и рассмотрели основные строковые функции SQL. В следующих статьях мы продолжим изучение SQL.
Преобразование регистра в PL/SQL: UPPER, LOWER, NLS_COMP, NLS_SORT
Регистр символов часто играет важную роль в работе со строками. Например, может потребоваться сравнить две строки независимо от регистра. Выбор решения этой проблемы зависит как от версии базы данных, так и от области действия выполняемых операций.
Преобразование строки к верхнему или нижнему регистру
Для решения проблем с регистром символов можно воспользоваться встроенной функцией UPPER или LOWER . Эти функции преобразуют всю строку за одну операцию. Пример:
В этом примере обе строки обрабатываются функцией LOWER , так что в итоговом сравнении участвуют строки ‘ andrew sears’ и ‘ andrew sears ‘.
Сравнение без учета регистра символов
Начиная с Oracle10g Release 2, появилась возможность использования параметров инициализации NLS_COMP и NLS_SORT для включения режима сравнения без учета регистра. Задайте параметру NLS_COMP значение LINGUISTIC ; тем самым вы прикажете Oracle использовать NLS_SORT для сравнений строк. Затем задайте параметру NLS_SORT значение, соответствующее сравнению без учета регистра — например, BINARY_CI или XWEST_EUROPEAN_CI . (Суффикс _CI означает «Case Insensitivity», то есть «Без учета регистра».) Приведенный далее простой пример показывает, какие проблемы решаются при помощи NLS_COMP . Требуется взять список имен и определить, какое из них должно стоять на первом месте:
В моей системе этот вызов LEAST возвращает строку ‘ JONATHAN ‘. Дело в том, что в порядке сортировки символы верхнего регистра предшествуют символам нижнего регистра. По умолчанию параметру NLS_COMP задается значение BINARY , при котором результат строковых сравнений, выполняемых такими функциями, как LEAST , определяется кодами символов.
Возможно, вы предпочитаете, чтобы функция LEAST игнорировала регистр символов и возвращала ‘jon’ вместо ‘ JONATHAN ‘. Измените значение NLS_COMP , чтобы при сортировке учитывалось значение NLS_SORT :
Теперь необходимо изменить NLS_SORT с указанием нужных правил сортировки.
По умолчанию переменная NLS_SORT часто равна BINARY , но значение может быть и другим в зависимости от конфигурации системы. В нашем примере будет использоваться сортировка BINARY_CI :
Попробуем снова вызвать LEAST :
На этот раз будет получен результат ‘ jon ‘. Пример кажется простым, но добиться такого результата без только что описанной сортировки по правилам языка будет нелегко.
Действие языковой сортировки распространяется не только на функции, но и на простые сравнения строк. Пример:
При указанных значениях параметров NLS_COMP и NLS_SORT выражение ‘ Jonathan ‘ = ‘ JONATHAN ‘ в этом примере равно TRUE .
Параметры NLS_COMP и NLS_SORT влияют на все операции со строками. Они продолжают действовать вплоть до их явного изменения или до завершения сеанса.
Oracle также поддерживает режим сортировки без учета диакритических знаков; чтобы включить его, присоедините к имени правила сортировки суффикс _AI (вместо _CI ).
За полным списком правил языковой сортировки обращайтесь к документации Oracle Database Globalization Support Guide. В этом руководстве также подробно объясняется действие параметров NLS_COMP и NLS_SORT .
Регистр символов и индексы
При работе со строками часто возникает необходимость в поиске и сравнении без учета регистра символов. Но если вы реализуете описанный здесь прием, вдруг выясняется, что ваше приложение перестает использовать индексы и начинает работать слишком медленно. Будьте внимательны, чтобы ваши действия не повредили использованию индексов в SQL. Для наглядности рассмотрим пример с демонстрационной таблицей hr.employees . Таблица employees использует индекс emp_name_ix для столбцов last_name , first_name . Мой код включает следующую команду SQL:
Изначально код использует индекс emp_name_ix , но когда я задаю параметры NLS_COMP=LINGUISTIC и NLS_SORT=BINARY_CI , чтобы включить поиск без учета регистра, индекс перестает использоваться, и операции выполняются с полным просмотром таблицы — со всеми вытекающими последствиями! Одно из возможных решений заключается в использовании индекса на базе функции, игнорирующего регистр символов:
Теперь при выполнении запросов без учета регистра символов используется индекс без учета регистра, а быстродействие программы остается на высоком уровне.
Преобразование первого символа к верхнему регистру
Кроме функций UPPER и LOWER , для преобразования регистра символов используется функция INITCAP . Она преобразует первую букву каждого слова в строке к верхнему регистру, а все остальные буквы — к нижнему регистру. Например, следующий фрагмент:
выводит следующий результат:
Идея использовать INITCAP для форматирования имен выглядит заманчиво, и все будет хорошо, пока вы не столкнетесь с особым случаем:
Правильное написание фамилии — «McWilliams», а не «Mcwilliams». Помните, что функция INITCAP временами удобна, но она выдает неверный результат для имен и слов, которые содержат более одной буквы в верхнем регистре.