Построение запросов sql. Программирование. Удаление пробелов из строки
Как и в большинстве языков программирования, в SQL существуют функции для обработки данных. Стоит отметить, что в отличие от SQL-операторов, функции не стандартизованы для всех видов СУБД, то есть для выполнения одних и тех же операции над данными, разные СУБД имеют свои собственные имена функций. Это означает, что код запроса написан в одной СУБД может не работать в другой, и это нужно учитывать в дальнейшем. Больше всего это касается функций для обработки текстовых значений, преобразования типов данных и манипуляций над датами.
Обычно СУБД поддерживается стандартный набор типов функций, а именно:
- Текстовые функции, которые используются для обработки текста (выделение части символов в тексте, определение длины текста, перевод символов в верхний или нижний регистр...)
- Числовые функции. Используются для выполнения математических операций над числовыми значениями
- Функции даты и времени (осуществляют манипулирования датой и временем, рассчитывают период между датами, проверяют даты на корректность и т.п.)
- Статистические функции (для вычисления максимальных /минимальных значений, средних значений, подсчет количества и суммы...)
- Системные функции (предоставляют разного рода служебную информацию о СУБД, пользователе и др..).
1. Функции SQL для обработки текста
Реализация SQL в СУБД Access имеет следующие функции для обработки текста:
Переведем названия товаров в верхний регистр с помощью функции UCase() :
SELECT Product, UCase(Product) AS Product_UCase FROM Sumproduct
Выделим первые три символа в тексте с помощью функции LEFT() :
SELECT Product, LEFT(Product, 3) AS Product_LEFT FROM Sumproduct
2. Функции SQL для обработки чисел
Функции обработки чисел предназначены для выполнения математических операций над числовыми данными. Эти функции предназначены для алгебраических и геометрических вычислений, поэтому они используются значительно реже функций обработки даты и времени. Однако числовые функции наиболее стандартизированными для всех версий SQL. Давайте взглянем на перечень числовых функций:
Мы привели лишь несколько основных функций, однако вы всегда можете обратиться к документации вашей СУБД, чтобы увидеть полный перечень функций, которые поддерживаются с их подробным описанием.
Например, напишем запрос для получения корня квадратного для чисел в столбце Amount с помощью функции SQR() :
SELECT Amount, SQR(Amount) AS Amount_SQR FROM Sumproduct
3. Функции SQL для обработки даты и времени
Функции манипулирования датой и временем являются одними из важнейших и часто используемых функций SQL. В базах данных значения дат и времени хранятся в специальном формате, поэтому их невозможно использовать напрямую без дополнительной обработки. Каждая СУБД имеет свой набор функций для обработки дат, что, к сожалению, не позволяет переносить их на другие платформы и реализации SQL.
Список некоторых функций для обработки даты и времени в СУБД Access :
Посмотрим на примере как работает функция DatePart() :
SELECT Date1, DatePart("m", Date1) AS Month1 FROM Sumproduct
Функция DatePart () имеет дополнительный параметр, который нам позволяет отобразить необходимую часть даты. В примере мы использовали параметр "m" , который отображает номер месяца (таким же образом мы можем отразить год - "yyyy" , квартал - "q " , день - " d " , неделю - " w " , час - " h " , минуты - "n" , секунды - "s" и т.д.).
4. Статистические функции SQL
Статистические функции помогают нам получить готовые данные без их выборки. SQL-запросы с этими функциями часто используются для анализа и создания различных отчетов. Примером таких выборок может быть: определение количества строк в таблице, получение суммы значений по определенному полю, поиск наибольшего /наименьшего или среднего значения в указанном столбце таблицы. Также отметим, что статистические функции поддерживаются всеми СУБД без особых изменений в написании.
Список статистических функций в СУБД Access :
COUNT() :
SELECT COUNT(*) AS Count1 FROM Sumproduct - возвращает количество всех строк в таблице
SELECT COUNT(Product) AS Count2 FROM Sumproduct - возвращает количество всех непустых строк в поле Product
Мы намеренно удалили одно значение в столбце Product , чтобы показать разницу в работе двух запросов.
Примеры использования функции SUM() :
SELECT SUM(Quantity) AS Sum1 FROM Sumproduct WHERE Month = "April "
Данным запросу мы отразили общее количество проданного товара в апреле.
SELECT SUM(Quantity*Amount) AS Sum2 FROM Sumproduct
Как видим, в статистических функциях мы можем осуществлять вычисления над несколькими столбцами с использованием стандартных математических операторов.
Пример использования функции MIN() :
SELECT MIN(Amount) AS Min1 FROM Sumproduct
Пример использования функции MAX() :
SELECT MAX(Amount) AS Max1 FROM Sumproduct
Пример использования функции AVG() :
SELECT AVG(Amount) AS Avg1 FROM Sumproduct
Здравствуйте, уважаемые читатели блога сайт. Сегодня я хотел бы поговорить о языке 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
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);
Результат: ква
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 ([ 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. Поэтому не забудьте подписаться на обновления блога . До новых встреч!
Для выделения строки в обратной последовательности (начиная с конца) используется функция REVERSE. Функция имеет следующий формат: REVERSE (<строковое выражение>).
Например, для вывода информации об абонентах, имеющих инициалы E.B., можно испрользовать следующий запрос:
SELECT * FROM Abonent
WHERE REVERSE (Fio) STARTING WITH REVERSE (Е.В.);.
Результат выполнения запроса представлен на рис. 3.20.
МИЩЕНКО Е.В. | |||||
СТАРОДУБЦЕВ Е.В. |
Рис. 3.20. Результат работы функции REVERSE
которого будут выделяться начале (для LEFT) или конце
Функции LEFT и RIGHT используются для выделения нужного количества символов из начала или конца определенной строки соответственно и имеют следующий формат:
LEFT (<строковое выражение>, длина),
RIGHT (<строковое выражение>, длина), где <строковое выражение> - выражение, из символы;
длина - количество выделяемых символов в (для RIGHT) строки.
Например, предыдущий запрос, реализованный с помощью функции REVERSE, можно реализовать с помощью функции RIGHT следующим образом:
SELECT * FROM Abonent WHERE RIGHT (Fio, 4) = Е.В.;.
Результат выполнения запроса будет совпадать с результатом, представленным на рис. 3.20.
Например, для вывода Fio абонентов, проживающих на улице с кодом 3, и первых 4-х цифр из номеров их лицевых счетов можно использовать следующий запрос:
SELECT Fio, LEFT (AccountCD, 4) FROM Abonent WHERE StreetCD = 3;. Результат выполнения запроса представлен на рис. 3.21.
АКСЕНОВ С. А. | |
МИЩЕНКО Е.В. | |
КОНЮХОВ В.С. |
Рис. 3.21. Результат работы функции RIGHT
Существует ряд функций для замены части исходной строки на другую последовательность символов.
Функция OVERLAY заменяет в исходной строке подстроку, начинающуюся с номера позиция и имеющую размер длина, на значение строки для замены. Функция имеет следующий формат:
OVERLAY (<исходная строка> PLACING <строка для замены>
FROM позиция [ FOR длина ]), где <исходная строка> и <строка для замены> могут представлять собой строковое выражение.
Если длина не указана, то по умолчанию принимается длина в символах строки для замены (CHAR LENGTH (<строка для замены>)).
Функция OVERLAY эквивалентна следующему выражению с использованием функции SUBSTRING:
SUBSTRING (<исходная строка> FROM 1 FOR (позиция - 1))
<строка для замены>
SUBSTRING (<исходная строка> FROM (позиция + длина)).
Функция REPLACE заменяет все вхождения <подстроки> в <строковое выражение> на указанную <строку для замены>. Имеет следующий формат:
REPLACE (<строковое выражение>, <подстрока>, <строка для замены>). Например, для замены в названии неисправностей слова плиты на словосочетание газовой плиты можно выполнить следующий запрос: SELECT REPLACE (FailureNM, плиты, газовой плиты)
FROM Disrepair;.
Результат выполнения запроса представлен на рис. 3.22.
Засорилась водогрейная колонка
Не горит АГВ
Течет из водогрейной колонки
Неисправна печная горелка
Неисправен газовый счетчик
Плохое поступление газа на горелку газовой плиты
Туго поворачивается пробка крана газовой плиты
При закрытии краника горелка газовой плиты не гаснет Неизвестна
Рис. 3.22. Результат работы функции REPLACE
К скалярным функциям относится также функция TRIM.
возвращает строку аргумента, удаляя символы (по умолчанию
начала и/или конца строки.
Для вызова функции используется следующий синтаксис: TRIM ([ [ <удаляемая подстрока> ] FROM ] <строковое выражение>).
Эта функция - пробелы) из
LEADING указывает на то, что надо удалить указанную подстроку из начала строки, TRAILING - из конца строки, BOTH - из начала и конца строки. При использовании функции следует учитывать следующее:
1) если LEADING, TRAILING или BOTH не указаны, то принимается BOTH по умолчанию;
2) если <удаляемая подстрока> не определена, то за нее принимается по умолчанию пустая строка;
3) если LEADING, или TRAILING, или BOTH и/или <удаляемая подстрока> указаны, то после слова FROM должно быть обязательно указано <строковое выражение>, из которого удаляются символы;
4) указание строки, из которой удаляются символы, после слова FROM не может быть использовано самостоятельно (если LEADING, TRAILING, BOTH и <удаляемая подстрока> не указаны).
Например, для вывода данных из таблицы Street, указывая название улицы без слова УЛИЦА, можно использовать следующий запрос: SELECT StreetCD,
TRIM (BOTH УЛИЦА FROM StreetNM) AS Str Name
Результат выполнения запроса представлен на рис. 3.23.
ВОИКОВ ПЕРЕУЛОК |
|
КУТУЗОВА |
|
МОСКОВСКАЯ |
|
МОСКОВСКОЕ ШОССЕ |
|
ТАТАРСКАЯ |
|
ГАГАРИНА |
|
ЦИОЛКОВСКОГО |
|
Рис. 3.23. Результат работы функции TRIM
При указании удаляемой подстроки следует учитывать регистр символов (строчные или прописные буквы). Если в предыдущем примере вместо УЛИЦА ввести улица, то запрос выдаст неверный результат, так как все названия улиц в таблице Street записаны в верхнем регистре символов.
Существуют функции LPAD и RPAD, которые дополняют строку аргумента слева (LPAD) или справа (RPAD) указанной последовательностью символов (<строка заполнитель>) до заданного размера (длина). В случае если <строка заполнитель> не указана, для дополнения используется пробел. Строка-заполнитель обрезается, когда результирующая строка достигает заданной длины. Функции имеют следующий формат:
LPAD(<строковое выражение>, длина [, <строка заполнитель> ]), RPAD(<строковое выражение>, длина [, <строка заполнитель> ]).
| ... [ 25 ] ... |
