В другие. Она имеет следующий синтаксис:

CONV(число,N,M)

Аргумент число находится в системе счисления с основанием N. Функция переводит его в систему счисления с основанием M и возвращает значение в виде строки.

Пример 1

Следующий запрос переводит число 2 из десятичной системы счисления в двоичную:

SELECT CONV(2,10,2);

Результат: 10

Для перевода числа 2E из шестнадцатиричной системы в десятичную требуется запрос:

SELECT CONV("2E",16,10);

Результат: 46

Функция CHAR() переводит ASCII-код в строки. Она имеет следующий синтаксис:

CHAR(n1,n2,n3..)

Пример 2

SELECT CHAR(83,81,76);

Результат: SQL

Следующие функции возвращают длину строки:

• LENGTH(строка);
• OCTET_LENGTH(строка);
• CHAR_LENGTH(строка);
• CHARACTER_LENGTH(строка).

Пример 3

SELECT LENGTH("MySQL");

Результат: 5

Иногда бывает полезной функция BIT_LENGTH(строка) , которая возвращает длину строки в битах.

Пример 4

SELECT BIT_LENGTH("MySQL");

Результат: 40

Функции работы с подстроками

Подстрокой обычно называют часть строки. Часто требуется узнать позицию первого вхождения подстроки в строку. Эту задачу в MySQL решают три функции:

• LOCATE(подстрока, строка [,позиция]);
• POSITION(подстрока, строка);
• INSTR(строка, подстрока).

Если подстрока не содержится в строке, то все три функции возвращают значение 0. Функция INSTR() отличается от двух других порядком аргументов. Функция LOCATE() может содержать третий аргумент позиция , который позволяет искать подстроку в строке не с начала, а с указанной позиции.

Пример 5

SELECT LOCATE("Топаз", "открытое акционерное общество Топаз");

Результат: 31

SELECT POSITION("Топаз", "открытое акционерное общество Топаз");

Результат: 31

SELECT INSTR("открытое акционерное общество Топаз",’Топаз’);

Результат: 31

SELECT LOCATE("Топаз", " Завод Топаз и ООО Топаз", 9);

Результат: 20

SELECT LOCATE("Алмаз", "открытое акционерное общество Топаз");

Результат: 0

Функции LEFT(строка, N) и RIGHT(строка, N) возвращают соответственно крайние левые и крайние правые N символов в строке.

Пример 6

SELECT LEFT("СУБД MySQL", 4);

Результат: СУБД

SELECT RIGHT("СУБД MySQL", 5);

Результат: MySQL

Иногда требуется получить подстроку, которая начинается с некоторой заданной позиции. Для этого используются функции:

• SUBSTRING(строка, позиция, N);
• MID(строка, позиция, N).

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

Пример 7

SELECT SUBSTRING("СУБД MySQL - одна из самых популярных СУБД", 6,5);

Результат: MySQL

При работе с электронными адресами и адресами сайтов очень полезна функция SUBSTR_INDEX() . Функция имеет три аргумента:

SUBSTR_INDEX(строка, разделитель, N).

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

Пример 8

SELECT SUBSTRING_INDEX("www.mysql.ru",".",2);

Результат: www.mysql

SELECT SUBSTRING_INDEX("www.mysql.ru",".",-2);

Результат: mysql.ru

Функция REPLACE(строка,подстрока1,подстрока2) позволяет заменить в строке все вхождения подстроки1 на подстроку2.

Строковые функции Sql

Эта группа функций позволяет манипулировать текстом. Строковых функций много, мы рассмотрим наиболее употребительные.

• CONCAT(str1,str2...) Возвращает строку, созданную путем объединения аргументов (аргументы указываются в скобках - str1,str2...). Например, в нашей таблице Поставщики (vendors) есть столбец Город (city) и столбец Адрес (address). Предположим, мы хотим, чтобы в результирующей таблице Адрес и Город указывались в одном столбце, т.е. мы хотим объединить данные из двух столбцов в один. Для этого мы будем использовать строковую функцию CONCAT(), а в качестве аргументов укажем названия объединяемых столбцов - city и address:

  SELECT CONCAT(city, address) FROM vendors;

  
  

  Обратите внимание, объединение произошло без разделения, что не очень читабельно. Давайте подправим наш запрос, чтобы между объединяемыми столбцами был пробел:

  SELECT CONCAT(city, " ", address) FROM vendors;

  
  

  Как видите, пробел считается тоже аргументом и указывается через запятую. Если объединяемых столбцов было бы больше, то указывать каждый раз пробелы было бы нерационально. В этом случае можно было бы использовать строковую функцию CONCAT_WS(разделитель, str1,str2...) , которая помещает разделитель между объединяемыми строками (разделитель указывается, как первый аргумент). Наш запрос тогда будет выглядеть так:

  SELECT CONCAT_WS(" ", city, address) FROM vendors;

  Результат внешне не изменился, но если бы мы объединяли 3 или 4 столбца, то код значительно бы сократился.



• INSERT(str, pos, len, new_str) Возвращает строку str, в которой подстрока, начинающаяся с позиции pos и имеющая длину len символов, заменена подстрокой new_str. Предположим, мы решили в столбце Адрес (address) не отображать первые 3 символа (сокращения ул., пр., и т.д.), тогда мы заменим их на пробелы:

  SELECT INSERT(address, 1, 3, " ") FROM vendors;

  
  

  То есть три символа, начиная с первого, заменены тремя пробелами.



• LPAD(str, len, dop_str) Возвращает строку str, дополненную слева строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы справа, а пустое пространство заполнялось бы точками:

  SELECT LPAD(city, 15, ".") FROM vendors;

  
  


• RPAD(str, len, dop_str) Возвращает строку str, дополненную справа строкой dop_str до длины len. Предположим, мы хотим, чтобы при выводе городов поставщиков они располагались бы слева, а пустое пространство заполнялось бы точками:

  SELECT RPAD(city, 15, ".") FROM vendors;

  
  

  Обратите внимание, значение len ограничивает количество выводимых символов, т.е. если название города будет длиннее 15 символов, то оно будет обрезано.



• LTRIM(str) Возвращает строку str, в которой удалены все начальные пробелы. Эта строковая функция удобна для корректного отображения информации в случаях, когда при вводе данных допускаются случайные пробелы:

  SELECT LTRIM(city) FROM vendors;



• RTRIM(str) Возвращает строку str, в которой удалены все конечные пробелы:

  SELECT RTRIM(city) FROM vendors;

  В нашем случае лишних пробелов не было, поэтому и результат внешне мы не увидим.



• TRIM(str) Возвращает строку str, в которой удалены все начальные и конечные пробелы:

  SELECT TRIM(city) FROM vendors;



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

  SELECT city, LOWER(city) FROM vendors;

  
  Видите, какая абракадабра получилась. А вот с латиницей все в порядке:

  SELECT LOWER("CITY");

  
  


• UPPER(str) Возвращает строку str, в которой все символы переведены в верхний регистр. С русскими буквами так же лучше не применять. А вот с латиницей все в порядке:

  SELECT UPPER(email) FROM customers;

  
  


• LENGTH(str) Возвращает длину строки str. Например, давайте узнаем сколько символов в наших адресах поставщиков:

  SELECT address, LENGTH(address) FROM vendors;

  
  


• LEFT(str, len) Возвращает len левых символов строки str. Например, пусть в городах поставщиков выводится только первые три символа:

  SELECT name, LEFT(city, 3) FROM vendors;

  
  


• RIGHT(str, len) Возвращает len правых символов строки str. Например, пусть в городах поставщиков выводится только последние три символа: SELECT LOAD_FILE("C:/proverka");
  Обратите внимание, необходимо указывать абсолютный путь к файлу .

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

Вот полный перечень функций работы со строками, взятый из BOL:

Результат - 11. Чтобы выяснить, какие это буквы, мы можем применить функцию CHAR, которая возвращает символ по известному ASCII-коду (от 0 до 255):

А вот как, например, можно получить таблицу кодов всех алфавитных символов:

SELECT CHAR(ASCII("a")+ num-1) letter, ASCII("a")+ num - 1 FROM (SELECT 5*5*(a-1)+5*(b-1) + c AS num FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) x CROSS JOIN (SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) y CROSS JOIN (SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5) z) x WHERE ASCII("a")+ num -1 BETWEEN ASCII("a") AND ASCII("z")

Тех, кто еще не в курсе генерации числовой последовательности, отсылаю к соответствующей статье.

Как известно, коды строчных и прописных букв отличаются. Поэтому чтобы получить полный набор без переписывания запроса, достаточно просто дописать к вышеприведенному коду аналогичный:

Я полагаю, что не будет сложным добавить эту букву в таблицу, если потребуется.

Рассмотрим теперь задачу определения нахождения искомой подстроки в строковом выражении. Для этого могут использоваться две функции - CHARINDEX и PATINDEX . Обе они возвращают начальную позицию (позицию первого символа подстроки) подстроки в строке. Функция CHARINDEX имеет синтаксис:

CHARINDEX (искомое_выражение , строковое_выражение [, стартовая_позиция ])

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

Следует отметить, что если искомая подстрока либо строковое выражение есть NULL, то результатом функции тоже будет NULL.

Следующий пример определяет позиции первого и второго вхождения символа "a" в имени корабля "California"

А вот, например, как можно найти имена кораблей, которые содержат последовательность из трех символов, первый и последний из которых есть "e":

Парная к LEFT функция RIGHT возвращает заданное число символов справа из строкового выражения:

RIGHT(<строковое выражения >,<число символов >)

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

Здесь мы разделяем пробелом имя класса и имя корабля. Кроме того, чтобы не повторять всю конструкцию в качестве аргумента функции, используем подзапрос. Результат будет иметь вид:

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

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

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

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

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

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

Основные строковые функции и операторы предоставляют разнообразные возможности и возвращают в качестве результата строковое значение. Некоторые строковые функции являются двухэлементными, что означает, что они могут работать одновременно с двумя строками. Стандарт SQL 2003 поддерживает строковые функции.

Оператор конкатенации

В SQL 2003 определяется оператор конкатенации (||), который соединяет две отдельные строки в одно строковое значение.

DB2 платформа

Платформа DB2 поддерживает оператор конкатенации SQL 2003, а также его синоним -функцию CONCAT.

MySQL платформа

Платформа MySQL поддерживает функцию CONCATQ - синоним оператора конкатенации SQL 2003.

Oracle и PostgreSQL

Платформы PostgreSQL и Oracle поддерживают оператор конкатенации SQL 2003 в виде двойной вертикальной черты.

SQL Server платформа

Платформа SQL Server использует знак плюса (+) в качестве синонима оператора конкатенации SQL 2003. В SQL Server есть системный параметр CONCAT_NULL_YIELDS_NULL, который регулирует поведение системы, если при конкатенации строковых значений встречаются значения NULL.

/* Синтаксис SQL 2003 */

stringl || string2 || string3

string1string2string3

/* Для MySQL */

CONCAT("stringl", "string2")

Если любое из соединяемых значений - пустое, то возвращается пустая строка. Кроме того, если в конкатенации принимает участие числовое значение, оно неявно преобразуется в строковое.

SELECT CONCAT("My ", "bologna ", "has ", "a ", "first ", "name…");

My bologna has a first name

SELECT CONCAT("My ", NULL, "has ", "first ", "name…");

CONVERT и TRANSLATE

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

Функция TRANSLATE переводит строковое значение из одного набора символов в другой. Так, функцию TRANSLATE можно использовать для преобразования значения из набора символов English в набор символов Kanji (японский) или Cyrillic (русский). Сам перевод уже должен существовать - либо заданный по умолчанию, либо созданный при помощи команды CREATE TRANSLATION.

Синтаксис SQL 2003

CONVERT (символьное_значение USING имя_символьного_преобразования)

TRANSLATE(символьное_значение USING имя_перевода)

Функция CONVERT преобразует символьное значение к набору символов с именем, указанным в параметре имя_символьного преобразования. Функция TRANSLATE преобразует символьное значение к набору символов, указанному в имени_перевода.

Среди рассматриваемых платформ только Oracle поддерживает функции CONVERT и TRANSLATE в том виде, в каком они определяются в стандарте SQL 2003. Реализация функции TRANSLATE в Oracle очень сходна с SQL 2003, но не идентична ему. В этой реализации функция принимает только два аргумента и производит перевод только между набором символов базы данных и набором символов с поддержкой национального языка.

Реализация функции CONV в MySQL только переводит числа с одного основания в другое. А вот в SQL Server реализация функции CONVERT весьма богата возможностями и изменяет тип данных для выражения, но во всех прочих своих аспектах она отличается от функции CONVERТстандарта SQL 2003. Платформа PostgreSQL не поддерживает функцию CONVERT, а реализация функции TRANSLATE преобразует все вхождения символьной строки в любую другую символьную строку.

DB2

Платформа DB2 не поддерживает функцию CONVERT, а поддержка функции TRANSLATE не соответствует стандарту ANSI. Функция TRANSLATE используется для преобразования подстрок и, как исторически сложилось, является синонимом функции UPPER, поскольку функция UPPER только недавно была добавлена в DB2. Если функция TRANSLATE используется в DB2 с единственным аргументом в виде символьного выражения, то результатом будет та же строка, преобразованная в верхний регистр. Если функция используется с несколькими аргументами, например TRANSLATE(ucmo4HUK, замена, совпадение), то функция преобразует все символы в источнике, которые также есть в параметре совпадение. Каждый символ в источнике, который находится в том же положении, что в параметре совпадение, будет заменен символом из параметра замена. Ниже приводится пример.

TRANSLATE("Hello, World! ") "HELLO; WORLD!"

TRANSLATE("Hello, World1", "wZ", "1W") "Hewwo, Zorwd1

MySQL

Платформа MySQL не поддерживает функции TRANSLATE и CONVERT.

Oracle

Платформа Oracle поддерживает следующий синтаксис функций CONVERT и TRANSLATE.

В реализации Oracle функция CONVERT возвращает текст символьного значения, преобразованный в целевой_набор_символов. Параметр символьное_значение - это строка, которую нужно преобразовать, параметр целевой_набор_символов - это название набора символов, в который нужно преобразовать строку, а параметр исходный _набор символов - это набор символов, в котором строковое значение изначально хранилось.

Функция TRANSLATE в Oracle соответствует синтаксису ANSI, но вы можете выбирать только один из двух наборов символов: набор символов базы данных (CHARJCS) и набор символов с поддержкой национального языка (NCHARJZS).

В Oracle также поддерживается другая функция, которая также называется TRANSLATE (без использования ключевого слова USING). Эта функция TRANSLATE никак не связана с преобразованием наборов символов.

Названия целевого и исходного наборов символов можно передавать либо в виде строковых констант, либо в виде ссылки на столбец таблицы. Обратите внимание, что при преобразовании строки в набор символов, в котором отображаются не все преобразуемые символы, можно подставлять символы-заменители.

Oracle поддерживает несколько общих наборов символов, к которым относятся наборы US7ASCII, WE8DECDEC. WE8HP, F7DEC, WE8EBCDIC500, WE8PC850u WE8ISO8859PI. Например:

SELECT CONVERT("Gro2", "US7ASCII", "WE8HP") FROM DUAL;

PostgreSQL

Платформа PostgreSQL поддерживает инструкцию CONVERT стандарта ANSI, а преобразования здесь можно определять при помощи команды CREATE CONVERSION. Реализация функции TRANSLATE в PostgreSQL предоставляет расширенный набор функций, которые позволяют преобразовать любой текст в другой текст в пределах указанной строки.

TRANSLATE (символьная строка, из_текста, в_текст)

Вот несколько примеров:

SELECT TRANSLATE("12345abcde", "5а", "XX"); "1234XXbcde" SELECT TRANSLATE(title, "Computer", "PC") FROM titles WHERE type="Personal_computer" SELECT CONVERT("PostgreSQL" USING iso_8859_1_to_utf_8) "PostgreSQL"

SQL Server

Платформа SQL Server не поддерживает функцию TRANSLATE. Реализация функции CONVERT в SQL Server не соответствует стандарту SQL 2003. Эта функция в SQL Server эквивалентна функции CAST.

CONVERT (тип_данных[(длина) | (точность, масштаб)], выражение, стиль])

Предложение стиль используется для определения формата преобразования даты. За дополнительной информацией обращайтесь к документации SQL Server. Ниже приводится пример.

SELECT title, CONVERT(char(7), ytd_sales) FROM titles ORDER BY title GO

Сегодня предлагаю рассмотреть простые примеры использования строковых функций Transact-SQL , и не просто описание и примеры некоторых функций, а их сочетание, т.е. как можно вкладывать их друг в друга, так как для реализации многих задач стандартных функций бывает недостаточно и приходится их использовать совместно. И поэтому мне хотелось бы показать Вам пару простых примеров написания таких запросов.

Мы с Вами уже рассматривали строковые функции SQL , но так как реализации данного языка в разных СУБД различны, например некоторых функций нет в Transact-SQL, а в PL/PGSql они есть, и как раз в прошлый раз мы рассматривали строковые функции, которые можно использовать в plpgsql и поэтому сегодня мы поговорим именно о Transact-SQL.

Как можно сочетать SUBSTRING, CHARINDEX и LEN

И так, к примеру, Вам необходимо искать в строке ее часть по определенному критерию и вырезать ее, и не просто искать однотипную часть, а динамически, т.е. для каждой строки строка поиска будет разная. Примеры будем писать в Management Studio SQL Server 2008.

Для этого мы будем использовать следующие функции:

• SUBSTRING (str, start, len) – данная функция вырезает часть строки из другой строки. Имеет три параметра 1. Это сама строка; 2. Начальная позиция, с какой необходимо начать вырезать; 3. Количество символов, сколько необходимо вырезать.
• CHARINDEX (str1, str2)- ищет str1 в str2 и возвращает порядковый номер первого символа в случае нахождения такой строки. Имеет третий не обязательный параметр, с помощью которого можно указать с какой стороны начинать поиск.
• LEN (str1)-длина строки, т.е. количество символов.

Как Вы видите, здесь я использовал объявление переменных, а Вы вместо переменных можете подставлять свои поля в запросе. Вот сам код:

Declare @rezult as varchar(10) --исходная строка declare @str1 as varchar(100) --строка поиска declare @str2 as varchar(10) set @str1 = "Пробная строка string для поиска в ней другой строки" set @str2 = "string" set @rezult=substring(@str1,CHARINDEX(@str2, @str1), LEN(@str2)) select @rezult

Смысл здесь вот в чем, мы, используя функцию len узнаем, сколько символов необходимо вырезать, а charindex задает ту позицию, с которой необходимо начинать вырезать, и соответственно substring выполняет саму выборку в строке.

Как можно сочетать LEFT, RIGHT и LEN

Допустим, что Вам необходимо получить первые несколько символов в строке или проверить эти первые символы в строке на наличие чего-либо, например, какой-то номер, а его длина естественно разная (пример естественно тестовый).

• Left (str, kol) – функции вырезает указанное количество символов с лева, имеет два параметра первой это строка а второй соответственно количество символов;
• Right (str, kol) — функции вырезает указанное количество символов с право, параметры те же самые.

Теперь будем использовать простые запросы к таблице

Для начала давайте создадим таблицу test_table:

CREATE TABLE ( IDENTITY(1,1) NOT NULL, (18, 0) NULL, (50) NULL, CONSTRAINT PRIMARY KEY CLUSTERED ( ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON ) ON GO

Заполним ее тестовыми данными и напишем вот такие запросы:

Как Вы понимаете первый запрос это просто выборка всех строк (Основы SQL — оператор select), а второй это уже непосредственно сочетание наших функций, вот код:

Select * from test_table select number, left(text,LEN(number)) as str from test_table

А если бы эти номера были справа, то мы бы использовали функцию RIGHT .

Использование Rtrim, Ltrim, Upper и Lower в сочетании

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

• Rtrim (str) –удаляет пробелы справа;
• Ltrim (str) – удаляет пробелы слева;
• Upper (str) – приводит строку в верхний регистр;
• Lower (str) — приводит строку в нижний регистр.

Как видите, для закрепления мы здесь использовали еще и Substring и Len . Смысл запроса прост, мы удаляем пробелы и справа и слева, затем приводим первый символ к верхнему регистру путем вырезания его, далее мы конкатенируем (оператор +) этот символ с оставшейся строкой. Вот код:

Declare @str1 as varchar(100) set @str1 = " тестовая строка с пробелами в начале и в конце " select @str1 select upper(substring(rtrim(ltrim(@str1)),1,1))+ lower(substring(rtrim(ltrim(@str1)),2,LEN(rtrim(ltrim(@str1)))-1))

На сегодня я думаю достаточно, и если Вам нравиться программировать на SQL то на этом сайте мы не раз затрагивали эту очень интересную тему, например.