Sql server строковые функции. Строковые функции SQL
Строковые функции 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");
Обратите внимание, необходимо указывать абсолютный путь к файлу .
Функции языка Transact-SQL могут быть агрегатными или скалярными. Эти типы функций рассматриваются в этой статье.
Агрегатные функции
Агрегатные функции выполняют вычисления над группой значений столбца и всегда возвращают одно значение результата этих вычислений. Язык Transact-SQL поддерживает несколько обычных агрегатных функций:
AVGВычисляет среднее арифметическое значение данных, содержащихся в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.
MIN и MAXОпределяют максимальное и минимальное значение из всех значений данных, содержащихся в столбце. Значения могут быть числовыми, строковыми или временными (дата/время).
SUMВычисляет общую сумму значений в столбце. Значения, над которыми выполняется вычисление, должны быть числовыми.
COUNTПодсчитывает количество значений, отличных от null в столбце. Функция count(*) является единственной агрегатной функцией, которая не выполняет вычисления над столбцами. Эта функция возвращает количество строк (независимо от того, содержат ли отдельные столбцы значения null).
COUNT_BIGАналогична функции count, с той разницей, что возвращает значение данных типа BIGINT.
Использование обычных агрегатных функций в инструкции SELECT будет рассматриваться в одной из следующих статей.
Скалярные функции
Скалярные функции Transact-SQL используются в создании скалярных выражений. (Скалярная функция выполняет вычисления над одним значением или списком значений, тогда как агрегатная функция выполняет вычисления над группой значений из нескольких строк.) Скалярные функции можно разбить на следующие категории:
числовые функции;
функции даты;
строковые функции;
системные функции;
функции метаданных.
Эти типы функций рассматриваются в последующих разделах.
Числовые функции
Числовые функции языка Transact-SQL - это математические функции для модифицирования числовых значений. Список числовых функций и их краткое описание приводится в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| ABS | ABS(n) | Возвращает абсолютное значение (т. е. отрицательные значения возвращаются, как положительные) числового выражения n. |
SELECT ABS(-5.320) -- Вернет 5.320 SELECT ABS(8.90) -- Вернет 8.90 |
| ACOS, ASIN, ATAN, ATN2 | ACOS(n), ASIN(n), ATAN(n), ATN2(n, m) | Обратные тригонометрические функции, вычисляющие арккосинус, арксинус, арктангенс значения n (для ATN2 вычисляется арктангенс n/m). Исходные значения n, m и результат имеют тип данных FLOAT. |
|
| COS, SIN, TAN, COT | COS(n), SIN(n), TAN(n), COT(n) | Тригонометрические функции, вычисляющие косинус, синус, тангенс, котангенс значения n. Результат имеет тип данных FLOAT. |
|
| DEGREES, RADIANS | DEGREES(n), RADIANS(n) | Функция DEGREES преобразует радианы в градусы, RADIANS соответственно наоборот. |
SELECT DEGREES(PI() / 4) -- Вернет 45 SELECT COS(RADIANS(60.0)) -- Вернет 0.5 |
| CEILING | CEILING(n) | Округляет число до большего целого значения. |
SELECT CEILING(-5.320) -- Вернет -5 SELECT CEILING(8.90) -- Вернет 9 |
| ROUND | ROUND(n, p, [t]) | Округляет значение n с точностью до p. Когда аргумент p положительное число, округляется дробная часть числа n, а когда отрицательное - целая часть. При использовании необязательного аргумента t, число n не округляется, а усекается (т.е. округляется в меньшую сторону). |
SELECT ROUND(5.3208, 3) -- Вернет 5.3210 SELECT ROUND(125.384, -1) -- Вернет 130.000 SELECT ROUND(125.384, -1, 1) -- Вернет 120.000 |
| FLOOR | FLOOR(n) | Округляет до меньшего целого значения. |
SELECT FLOOR(5.88) -- Вернет 5 |
| EXP | EXP(n) | Вычисляет значение e n . |
|
| LOG, LOG10 | LOG(n), LOG10(n) | LOG(n) - вычисляет натуральный логарифм (т.е. с основанием e) числа n, LOG10(n) - вычисляет десятичный (с основанием 10) логарифм числа n. |
|
| PI | PI() | Возвращает значение π (3,1415). |
|
| POWER | POWER(x, y) | Вычисляет значение x y . |
|
| RAND | RAND() | Возвращает произвольное число типа FLOAT в диапазоне значений между 0 и 1. |
|
| ROWCOUNT_BIG | ROWCOUNT_BIG() | Возвращает количество строк таблицы, которые были обработаны последней инструкцией Transact-SQL, исполненной системой. Возвращаемое значение имеет тип BIGINT. |
|
| SIGN | SIGN(n) | Возвращает знак значения n в виде числа: +1, если положительное, -1, если отрицательное. |
|
| SQRT, SQUARE | SQRT(n), SQUARE(n) | SQRT(n) - вычисляет квадратный корень числа n, SQUARE(n) - возвращает квадрат аргумента n. |
Функции даты
Функции даты вычисляют соответствующие части даты или времени выражения или возвращают значение временного интервала. Поддерживаемые в Transact-SQL функции даты и их краткое описание приводятся в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| GETDATE | GETDATE() | Возвращает текущую системную дату и время. |
SELECT GETDATE() |
| DATEPART | DATEPART (item, date) | Возвращает указанную в параметре item часть даты date в виде целого числа. |
Вернет 1 (Январь) SELECT DATEPART(month, "01.01.2012") -- Вернет 4 (Wednesday) SELECT DATEPART(weekday, "02.01.2012") |
| DATENAME | DATENAME (item, date) | Возвращает указанную в параметре item часть даты date в виде строки символов. |
Вернет January SELECT DATENAME(month, "01.01.2012") -- Вернет Wednesday SELECT DATENAME(weekday, "02.01.2012") |
| DATEDIFF | DATEDIFF (item, dat1, dat2) | Вычисляет разницу между двумя частями дат dat1 и dat2 и возвращает целочисленный результат в единицах, указанных в аргументе item. |
Вернет 19 (19 лет промежуток между датами) SELECT DATEDIFF(year, "01.01.1990", "01.01.2010") -- Вернет 7305 (7305 дней промежуток между датами) SELECT DATEDIFF(day, "01.01.1990", "01.01.2010") |
| DATEADD | DATEADD (item, n, date) | Прибавляет n-е количество единиц, указанных в аргументе item к указанной дате date. (Значение аргумента n также может быть отрицательным.) |
Прибавит 3 дня к текущей дате SELECT DATEADD(day, 3, GETDATE()) |
Строковые функции
Строковые функции манипулируют значениями столбцов, которые обычно имеют символьный тип данных. Поддерживаемые в Transact-SQL строковые функции и их краткое описание приводятся в таблице ниже:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| ASCII, UNICODE | ASCII(char), UNICODE(char) | Преобразовывает указанный символ в соответствующее целое число кода ASCII. |
SELECT ASCII("W") -- 87 SELECT UNICODE("ю") -- 1102 |
| CHAR, NCHAR | CHAR(int), NCHAR(int) | Преобразовывает код ASCII (или Unicode если NCHAR) в соответствующий символ. |
SELECT CHAR(87) -- "W" SELECT NCHAR(1102) -- "ю" |
| CHARINDEX | CHARINDEX (str1, str2) | Возвращает начальную позицию вхождения подстроки str1 в строку str2. Если строка str2 не содержит подстроки str1, возвращается значение 0 |
Вернет 5 SELECT CHARINDEX ("морф", "полиморфизм") |
| DIFFERENCE | DIFFERENCE (str1, str2) | Возвращает целое число от 0 до 4, которое является разницей между значениями SOUNDEX двух строк str1 и str2. Метод SOUNDEX возвращает число, которое характеризует звучание строки. С помощью этого метода можно определить подобно звучащие строки. Работает только для символов ASCII. |
Вернет 2 SELECT DIFFERENCE ("spelling", "telling") |
| LEFT, RIGHT | LEFT (str, length), RIGHT (str, length) | Возвращает количество первых символов строки str, заданное параметром length для LEFT и последние length символов строки str для функции RIGHT. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "Синх" SELECT LEFT(@str, 4) -- Вернет "зация" SELECT RIGHT(@str, 5) |
| LEN | LEN(str) | Возвращает количество символов (не количество байт) строки str, указанной в аргументе, включая конечные пробелы. |
|
| LOWER, UPPER | LOWER(str), UPPER(str) | Функция LOWER преобразовывает все прописные буквы строки str1 в строчные. Входящие в строку строчные буквы и иные символы не затрагиваются. Функция UPPER преобразовывает все строчные буквы строки str в прописные. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "СИНХРОНИЗАЦИЯ" SELECT UPPER(@str) -- Вернет "синхронизация" SELECT LOWER(@str) |
| LTRIM, RTRIM | LTRIM(str), RTRIM(str) | Функция LTRIM удаляет начальные пробелы в строке str, RTRIM соответственно удаляет пробелы в конце строки. |
|
| QUOTENAME | QUOTENAME (char_string) | Возвращает строку в кодировке Unicode с добавленными ограничителями, чтобы преобразовать строку ввода в действительный идентификатор с ограничителями. |
DECLARE @str nvarchar(30) = "Синхронизация"; -- Вернет "[Синхронизация]" SELECT QUOTENAME(@str) |
| PATINDEX | PATINDEX (%p%, expr) | Возвращает начальную позицию первого вхождения шаблона p в заданное выражение expr, или ноль, если данный шаблон не обнаружен. |
Вернет 4 SELECT PATINDEX("%хро%", "Синхронизация") |
| REPLACE | REPLACE (str1, str2, str3) | Заменяет все вхождения подстроки str2 в строке str1 подстрокой str3. |
Вернет "Десинхронизация" SELECT REPLACE("Синхронизация", "Синхр", "Десинхр") |
| REPLICATE | REPLICATE (str, i) | Повторяет i раз строку str. |
Вернет "aBaBaBaBaB" SELECT REPLICATE("aB", 5) |
| REVERSE | REVERSE (str) | Выводит строку str в обратном порядке. |
Вернет "яицазинорхниС" SELECT REVERSE("Синхронизация") |
| SOUNDEX | SOUNDEX (str) | Возвращает четырехсимвольный код soundex, используемый для определения похожести двух строк. Работает только для символов ASCII. |
|
| SPACE | SPACE (length) | Возвращает строку пробелов длиной, указанной в параметре length. Аналог REPLICATE(" ", length). |
|
| STR | STR (f[, len[, d]]) | Преобразовывает заданное выражение с плавающей точкой f в строку, где len - длина строки, включая десятичную точку, знак, цифры и пробелы (по умолчанию равно 10), а d - число разрядов дробной части, которые нужно возвратить. |
Вернет "3.14" SELECT STR (3.1415, 4, 2) |
| STUFF | STUFF (str1, a, length, str2) | Удаляет из строки str1 length-символов, начиная с позиции a, и вставляет на их место строку str2. |
Note in a book SELECT STUFF("Notebook", 5, 0," in a ") -- Handbook SELECT STUFF("Notebook", 1, 4, "Hand") |
| SUBSTRING | SUBSTRING (str1, a, length) | Извлекает из строки str, начиная с позиции a, подстроку длиной length. |
Системные функции
Системные функции языка Transact-SQL предоставляют обширную информацию об объектах базы данных. Большинство системных функций использует внутренний числовой идентификатор (ID), который присваивается каждому объекту базы данных при его создании. Посредством этого идентификатора система может однозначно идентифицировать каждый объект базы данных.
В следующей таблице приводятся некоторые из наиболее важных системных функций вместе с их кратким описанием:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| CAST | CAST (w AS type [(length)] | Преобразовывает выражение w в указанный тип данных type (если это возможно). Аргумент w может быть любым действительным выражением. |
Вернет 3 SELECT CAST (3.1258 AS INT) |
| COALESCE | COALESCE (a1, a2) | Возвращает первое значение выражения из списка выражений a1, a2, ..., которое не является значением null. |
|
| COL_LENGTH | COL_LENGTH (obj, col) | Возвращает длину столбца col объекта базы данных (таблицы или представления) obj. |
Вернет 4 SELECT COL_LENGTH ("Employee", "Id") |
| CONVERT | CONVERT (type[(length)], w) | Эквивалент функции CAST, но аргументы указываются по-иному. Может применяться с любым типом данных. |
|
| CURRENT_TIMESTAMP | CURRENT_TIMESTAMP | Возвращает текущие дату и время. |
|
| CURRENT_USER | CURRENT_USER | Возвращает имя текущего пользователя. |
|
| DATALENGTH | DATALENGTH (z) | Возвращает число байтов, которые занимает выражение z. |
Этот запрос возвращает длину каждого поля SELECT DATALENGTH(FirstName) FROM Employee |
| GETANSINULL | GETANSINULL ("dbname") | Возвращает 1, если использование значений null в базе данных dbname отвечает требованиям стандарта ANSI SQL. |
|
| ISNULL | ISNULL (expr, value) | Возвращает значение выражения expr, если оно не равно NULL; в противном случае возвращается значение value. |
|
| ISNUMERIC | ISNUMERIC (expr) | Определяет, имеет ли выражение expr действительный числовой тип. |
|
| NEWID | NEWID() | Создает однозначный идентификационный номер ID, состоящий из 16-байтовой двоичной строки, предназначенной для хранения значений типа данных UNIQUEIDENTIFIER. |
|
| NEWSEQUENTIALID | NEWSEQUENTIALID() | Создает идентификатор GUID, больший, чем любой другой идентификатор GUID, созданный ранее этой функцией на указанном компьютере. (Эту функцию можно использовать только как значение по умолчанию для столбца.) |
|
| NULLIF | NULLIF (expr1, expr2) | Возвращает значение null, если значения выражений expr1 и expr2 одинаковые. |
Запрос возвращает NULL для проекта, -- у которого Number = "p1" SELECT NULLIF(Number, "p1") FROM Project |
| SERVERPROPERTY | SERVERPROPERTY (propertyname) | Возвращает информацию о свойствах сервера базы данных. |
|
| SYSTEM_USER | SYSTEM_USER | Возвращает ID текущего пользователя. |
|
| USER_ID | USER_ID () | Возвращает идентификатор пользователя username. Если пользователь не указан, то возвращается идентификатор текущего пользователя. |
|
| USER_NAME | USER_NAME () | Возвращает имя пользователя с указанным идентификатором id. Если идентификатор не указан, то возвращается имя текущего пользователя. |
Функции метаданных
По большому счету, функции метаданных возвращают информацию об указанной базе данных и объектах базы данных. В таблице ниже приводятся некоторые из наиболее важных функций метаданных вместе с их кратким описанием:
| Функция | Синтаксис | Описание | Пример использования |
|---|---|---|---|
| COL_NAME | COL_NAME (tab_id, col_id) | Возвращает имя столбца с указанным идентификатором col_id таблицы с идентификатором tab_id. |
Вернет имя столбца "LastName" SELECT COL_NAME (OBJECT_ID("Employee"), 3) |
| COLUMNPROPERTY | COLUMNPROPERTY (id, col, property) | Возвращает информацию об указанном столбце. |
Вернет значение свойства PRECISION -- для столбца Id таблицы Employee SELECT COLUMNPROPERTY (OBJECT_ID("Employee"), "Id", "precision") |
| DATABASEPROPERTY | DATABASEPROPERTY (database, property) | Возвращает значение свойства property базы данных database. |
Вернет значение свойства IsNullConcat -- для базы данных SampleDb SELECT DATABASEPROPERTY ("SampleDb", "IsNullConcat") |
| DB_ID | DB_ID () | Возвращает идентификатор базы данных db_name. Если имя базы данных не указано, то возвращается идентификатор текущей базы данных. |
|
| DB_NAME | DB_NAME () | Возвращает имя базы данных, имеющей идентификатор db_id. Если идентификатор не указан, то возвращается имя текущей базы данных. |
|
| INDEX_COL | INDEX_COL (table, i, no) | Возвращает имя индексированного столбца таблицы table. Столбец указывается идентификатором индекса i и позицией no столбца в этом индексе. |
|
| INDEXPROPERTY | INDEXPROPERTY (obj_id, index_name, property) | Возвращает свойства именованного индекса или статистики для указанного идентификационного номера таблицы, имя индекса или статистики, а также имя свойства. |
|
| OBJECT_NAME | OBJECT_NAME (obj_id) | Возвращает имя объекта базы данных, имеющего идентификатор obj_id. |
SELECT OBJECT_NAME(245575913); |
| OBJECT_ID | OBJECT_ID (obj_name) | Возвращает идентификатор объекта obj_name базы данных. |
Вернет 245575913 - ID таблицы Employee SELECT OBJECT_ID("Employee") |
| OBJECTPROPERTY | OBJECTPROPERTY (obj_id, property) | Возвращает информацию об объектах из текущей базы данных. |
Сегодня предлагаю рассмотреть простые примеры использования строковых функций 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 то на этом сайте мы не раз затрагивали эту очень интересную тему, например.
В другие. Она имеет следующий синтаксис:
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.
