В четверг 10 октября 2024 г. в 13:10 завершился вебинар "Инструменты миграции: подходы к переносу данных"
Вебинар провели Роман Лавринчук и Александр Любушкин. Программа вебинара https://forsd.ru/migration_webcast_october
Запись вебинара будет опубликована позже. Следите за нашими новостями.
Приглашаем на вебинар "Обзор продуктов компании Axiom " (четверг 24 октября 2024 г. в 12:00).
Тема: импортозамещение в области корпоративной Java-разработки: JDK, сервер приложений, контейнеры Docker
Докладчик: Иван Диканев. Программа вебинара и регистрация: https://forsd.ru/axiom_24web

SQLite. Полезные выражения (Useful expressions)

Здесь будем спрашивать и отвечать на вопросы по другим СУБД. По мере наполнения и проявления тенденций будем разносить сообщения, создавая новые форумы.
Ответить
Аватара пользователя
SQL*Plus
Сообщения: 250
Зарегистрирован: Ср апр 20, 2022 1:09 pm

SQLite. Полезные выражения (Useful expressions)

Сообщение SQL*Plus »

Коллеги!

Предлагаю в этой теме размещать полезные выражения для решения разного рода задач средствами SQLite.

Это могут быть и ваши личные находки, и результаты поиска решений а интренете (приведите ссылку на источник).

Спасибо!
Вернуться к началу
Аватара пользователя
SQL*Plus
Сообщения: 250
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: SQLite. Полезные выражения (Useful expressions)

Сообщение SQL*Plus »

Мне потребовалось преобразовать IP-адрес (v4) в целое число (для правильной сортировки)

Нашел в сети такое решение:
https://stackoverflow.com/questions/230 ... ses-in-sql

Код: Выделить всё

 , 256*256*256 * CAST(substr(trim(ipv4),1,instr(trim(ipv4),'.')-1) AS INTEGER) 
 +     256*256 * CAST(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')-1) AS INTEGER) 
 +         256 * CAST(substr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')-1) AS INTEGER) 
 +           1 * CAST(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+ length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+1,length(trim(ipv4))) AS INTEGER)
   AS ipv4_as_integer
Пример выполнения:

Код: Выделить всё

WITH ADDR AS (
SELECT '192.168.1.2' AS ipv4 union all
SELECT '10.10.5.15' union all
SELECT '2.168.1.2'  union all
SELECT '8.8.8.8'    union all
SELECT '17.16.5.22' union all
SELECT '22.8.5.7'
)
--
-- https://stackoverflow.com/questions/23092783/best-way-to-sort-by-ip-addresses-in-sql
SELECT ipv4, 
   256*256*256 * CAST(substr(trim(ipv4),1,instr(trim(ipv4),'.')-1) AS INTEGER) 
 +     256*256 * CAST(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')-1) AS INTEGER) 
 +         256 * CAST(substr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')-1) AS INTEGER) 
 +           1 * CAST(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+ length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+length(substr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)) ,1, instr(substr(trim(ipv4),length(substr(trim(ipv4),1,instr(trim(ipv4),'.')))+1,length(ipv4)),'.')))+1,length(trim(ipv4))) AS INTEGER)
   AS ipv4_as_integer
FROM addr
ORDER BY ipv4_as_integer;

ipv4         ipv4_as_integer
-----------  ---------------
2.168.1.2    44564738       
8.8.8.8      134744072      
10.10.5.15   168428815      
17.16.5.22   286262550      
22.8.5.7     369624327      
192.168.1.2  3232235778
Еще раз результат выполнения:

Код: Выделить всё

ipv4         ipv4_as_integer
-----------  ---------------
2.168.1.2    44564738       
8.8.8.8      134744072      
10.10.5.15   168428815      
17.16.5.22   286262550      
22.8.5.7     369624327      
192.168.1.2  3232235778
Вернуться к началу
Аватара пользователя
SQL*Plus
Сообщения: 250
Зарегистрирован: Ср апр 20, 2022 1:09 pm

Re: SQLite. Полезные выражения (Useful expressions)

Сообщение SQL*Plus »

Такое "страшное" сложное выражение получилось потому, что в SQLite функция INSTR
не имеет еще двух параметров (третьего и четвертого), как например в Oracle Database:

INSTR(string , substring [, position [, occurrence ] ])

position is an nonzero integer indicating the character of string where Oracle Database begins the search—that is, the position of the first character of the first substring to compare with substring. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

occurrence is an integer indicating which occurrence of substring in string Oracle should search for. The value of occurrence must be positive. If occurrence is greater than 1, then the database does not return on the first match but continues comparing consecutive substrings of string, as described above, until match number occurrence has been found.
Что в переводе DeepL означает: писал(а):position - ненулевое целое число, указывающее на символ строки, с которого Oracle Database начинает поиск, то есть на позицию первого символа первой подстроки, которую нужно сравнить с подстрокой. Если позиция отрицательна, то Oracle считает в обратном направлении от конца строки, а затем выполняет поиск в обратном направлении от полученной позиции.

occurrence - целое число, указывающее, какое вхождение подстроки в строку Oracle должен искать. Значение вхождения должно быть положительным. Если вхождение больше 1, то база данных не возвращается при первом совпадении, а продолжает сравнивать последовательные подстроки строки, как описано выше, пока не будет найдено вхождение с номером.
Вернуться к началу
Ответить

Вернуться в «Другие СУБД»