Из этого урока вы узнаете, как можно менять представление получаемых в запросе данных с помощью целого ряда функций. Некоторые из этих функций определены стандартом ANSI, другие же основаны на стандартных или являются вариациями функций, чаще всего используемых в реализациях SQL.
Основными на этом уроке будут следующие темы.
• Обзор функций для работы со строками символов
• Как и когда используются функции для работы со строками
• Примеры функций, определяемых ANSI SQL
• Примеры функций, общих для основных реализаций SQL
• Обзор функций преобразования
• Как и когда используются функции преобразования
Функции стандарта ANSI для работы со строками
Функции для работы с символьными строками используются в SQL для представления данных в формате, отличном от того, в котором эти данные хранятся в таблице базы данных. Сначала мы обсудим те функции, которые включены в стандарт ANSI, а затем рассмотрим примеры использования функций, предлагаемых основными реализациями SQL. Из функций, определяемых стандартом ANSI, мы рассмотрим CONCATENATION, SUBSTRING, TRANSLATE, REPLACE, UPPER И LOWER.
Конкатенация
Конкатенация - это соединение двух строк в одну. Например, можно соединить имя и фамилию персоны в одну строку.
В результате конкатенации строк джон и СМИТ получается строка джон СМИТ.
Выделение частей строки
Подстрока (substring) - это строка символов, входящая в данную строку, другими словами, часть данной строки Например, следующие строковые значения являются подстроками строки ДЖОНСОН
Д ДЖОН ДЖО ОН СОН
Трансляция
Для посимвольной трансляции строк (т е замены одних символов на другие) используется функция TRANSLATE Обычно функция TRANSLATE имеет три аргумента конвертируемую строку, список заменяемых символов и список символов, на которые заменяются символы из первого списка Примеры использования функции будут рассмотрены позже
Общие функции для работы со строками
Функции для работы со строками используются для того, чтобы сравнивать, соединять, искать строки и выделять сегменты строк или значений столбцов В распоряжении программиста SQL предлагает целый ряд таких функций
В следующих разделах мы рассмотрим использование концепций ANSI в основных реализациях SQL, к которым относятся Oracle, Sybase, SQLBase, Informix и SQL Server
Обсуждаемые в данной книге концепции ANSI являются не более, чем концепциями Предлагаемые ANSI стандарты являются только рекомендациями, которые должны использовать и учитывать разработчики при создании реализаций SQL для реляционных баз данных В связи с этим, всегда помните о том, что обсуждаемые здесь функции не обязательно должны в точности соответствовать тем, которые вы найдете в используемой вами конкретной реализации SQL Конечно, подходы будут совпадать и работать функции будут практически так же, но вот имена функций и их синтаксис могут оказаться другими
Конкатенация
Конкатенация, как и многие другие функции, в разных реализациях SQL представлена по-разному Вот примеры использования конкатенации в Oracle и SQL Server
Oracle__________
SELECT 'ДЖОН' || 'СОН' возвратит ДЖОНСОН
SQL Server_____
SELECT 'ДЖОH' + 'СОН' возвратит ДЖОНСОН
Синтаксис использования конкатенации в Oracle следующий
ИМЯ_СТРОКИ || ["II] ИМЯ_СТРОКИ [ ИМЯ_СТРОКИ ]
Синтаксис использования конкатенации в SQL Server следующий.
ИМЯ_СТРОКИ + [ " + ] ИМЯ_СТРОКИ [ ИМЯ_СТРОКИ ]
Пример_______________________________Значение________
SELECT CITY + STATE Этот оператор SQL Server объединяет названия
FROM EMPLOYEEJTBL; города и штата в одну строку
SELECT CITY ||', '|| STATE Этот оператор Oracle объединяет названия го-
FROM EMPLOYEE_TBL; рода и штата в одну строку, помещая междуними запятую
SELECT CITY + ' ' + STATE Этот оператор SQL Server объединяет названия
FROM EMPLOYEE_TBL; города и штата в одну строку, помещая между ними пробел
Пример
SELECT LAST_NAME || ', ' || FIRST_NAME
FROM EMPLOYEEJTBL;
NAME
STEPHENS, TINA
PLEW, LINDA
GLASS, BRANDON
GLASS, JACOB
WALLACE, MARIAH
SPURGEON, TIFFANY
6 строк выбраны.
Обратите внимание на использование одиночных кавычек и запятой в этом операторе SQL Используя кавычки в строку можно добавить почти любой символ В некоторых реализациях SQL вместо одиночных кавычек могут использоваться двойные
Функция TRANSLATE
Функция TRANSLATE заменяет символы в строке символов в соответствии с указанным правилом замены Правило замены определяется посимвольным соответствием пары заданных буквальных символьных значений Синтаксис оператора функции TRANSLATE Следующий
TRANSLATE(МНОЖЕСТВО СТРОК, ЗНАЧЕНИЕ1, ЗНАЧЕНИЕ2)
Пример______________________Значение_____________________
SELECT TRANSLATE Этот оператор SQL заменяет все имеющиеся в строке
(CITY, ' IND ' , 'ABC' символы I на А, N на В и D на С
FROM EMPLOYEEJTBL;
Вот пример использования функции TRANSLATE с реальными данными.
SELECT CITY, TRANSLATE(CITY,'IND",'ABC')
FROM EMPLOYEE_TBL;
CITY TRANSLATE(CI
GFEENWOOD GREEBWOOC
INDIANAPOLIS ABCAABAPOLAS
WHITELAND WHATELABC
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS
INDIANAPOLIS ABCAABAPOLAS
6 строк выбраны.
Здесь все встречающиеся символы I заменены на А, N - на в и D - на с. В названии города INDIANAPOLIS подстрока IND была заменена на ABC, а в названии города GREENWOOD символ D был заменен на с. Обратите также внимание на то, каким образом транслировалось название города WHITELAND.
Функция REPLACE
Функция REPLACE используется для замены в строке некоторого заданного символа (или строки символов) другим заданным символом (строкой символов). Используется эта функция подобно функции TRANSLATE, только здесь заданный символ или строка заменяется другим заданным символом или строкой.
Синтаксис соответствующего оператора следующий.
REPLACE''ЗНАЧЕНИЕ', 'ЗНАЧЕНИЕ', [ NULL ] 'ЗНАЧЕНИЕ'}
Пример_______________________Значение________________________
SELECT Этот оператор возвращает список имен, заменив в
REPLACE (FIRST_NAME, них все встречающиеся буквы т на в
'T' , 'B')
FROM EMPLOYEEJTBL;
SELECT CITY, REPLACE(CITY, 'I' , 'Z')
FROM EMPLOYEE_TBL;
CITY TRANSLATE(CI
GREENWOOD GREENWOOD
INDIANAPOLIS ZNDZANAPOLZS
WHITELAND WHZTELAND
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS
INDIANAPOLIS ZNDZANAPOLZS
6 строк выбраны.
Функция UPPER
В большинстве реализаций SQL имеются средства для изменения регистра символов с помощью функций. Функция UPPER используется для изменения регистра символов заданной строки с нижнего на верхний.
Синтаксис соответствующего оператора следующий.
UPPER(строка символов)
Пример___________________________Значение_____________________
SELECT UPPER (LAST_NAME) Этот оператор изменяет регистр всех символов
FROM EMPLOYEE_TBL, в заданном столбце с нижнего на верхний
SELECT UPPER(CITY)
FROM EMPLOYEE_TBL;
UPPER(CITY)
GREENWOOD
INDIANAPOLIS
WHITELAND
INDIANAPOLIS
INDIANAPOLIS
INDIANAPOLIS
б строк выбраны.
Функция LOWER
В противоположность функции UPPER, функция LOWER используется для изменения регистра символов заданной строки с верхнего на нижний. Синтаксис соответствующего оператора следующий.
LOWER(строка символов)
Пример____________________________Значение____________________
SELECT LOWER (LAST_NAME) Этот оператор изменяет регистр всех символов в
FROM EMPLOYEEJTBL, заданном столбце с верхнего на нижний
SELECT LOWER(CITY)
FROM EMPLOYEEJTBL;
LOWER(CITY)
greenwood
Indianapolis
whiteland
Indianapolis
indianapolis
indianapolis
6 строк выбраны.
Функция SUBSTR
Функции для выделения подстрок из строк имеются во всех реализациях SQL, но имена таких функций могут отличаться, как видно из следующих примеров для Oracle и SQL Server.
В Oracle синтаксис соответствующего оператора следующий.
SUBSTR(имя столбца, начальная позиция, длина)
В случае SQL Server синтаксис соответствующего оператора будет следующим.
SUBSTRING(имя столбца, начальная позиция, длина)
Как видите, отличаются только имена функций.
Пример__________________________________Значение___________
SELECT SUBSTRING (EMP_ID,1,3) Этот оператор SQL возвращает первые три
FROM EMPLOYEE_TBL; символа строки EMP_ID
SELECT SUBSTRING (EMP_ID,4,2) Этот оператор SQL возвращает четвертый
FROM EMPLOYEE_TBL; ипятый символы строки EMP_ID
SELECT SUBSTRING (EMP_ID,6,4) Этот оператор SQL возвращает символы
FROM EMPLOYEE_TBL; с шестого по девятый включительно
строки EMP_ID
Вот пример использования данной функции в SQL Server.
SELECT EMP_ID, SUBSTRING(EMP_ID,1,3)
FROM EMPLOYEE_TBL;
EMP__ID SUB
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443
6 rows affected.
А вот пример использования данной функции в Oracle.
SELECT EMP_ID, SUBSTR(EMP_ID,1, 3)
FROM EMPLOYEEJTBL;
EMP_ID SUB
311549902 311
442346889 442
213764555 213
313782439 313
220984332 220
443679012 443
6 rows selected.
Обратите внимание на выводимые в этих двух случаях сообщения базы данных. В первом случае это 6 rows affected, а во втором - 6 rows selected. Оба сообщения имеют одинаковый смысл (6 строк выбраны), но такого рода отличия нередко можно увидеть при сравнении различных реализаций SQL.
