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

SQL*Plus · Вт июл 09, 2024 1:57 pm

Коллеги!

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

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

Спасибо!

SQL*Plus · Чт сен 19, 2024 6:15 pm

Мне потребовалось преобразовать 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 · Чт сен 19, 2024 6:24 pm

Такое "страшное" сложное выражение получилось потому, что в 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, то база данных не возвращается при первом совпадении, а продолжает сравнивать последовательные подстроки строки, как описано выше, пока не будет найдено вхождение с номером.

SQL*Plus · Вт фев 25, 2025 2:18 pm

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

Как это можно сделать в SQLite?

Используем функции "подрезки" - LTRIM или RTRIM или TRIM.

Пример для функции TRIM

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

sqlite> .nullvalue <NULL>

WITH t AS (SELECT '123456789 % ABCDEFG' AS txt UNION ALL
           SELECT '123456789 % ABCcEFG' UNION ALL
           SELECT 'BCD_1254' UNION ALL
           SELECT NULL)
, tri AS (
SELECT txt, TRIM(txt, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789') AS trimmed FROM t)
--
SELECT txt, trimmed, LENGTH(trimmed), IFNULL(LENGTH(trimmed), 0) FROM tri;

+---------------------+---------+-----------------+----------------------------+
|         txt         | trimmed | LENGTH(trimmed) | IFNULL(LENGTH(trimmed), 0) |
+---------------------+---------+-----------------+----------------------------+
| 123456789 % ABCDEFG |  %      | 3               | 3                          |
| 123456789 % ABCcEFG |  % ABCc | 7               | 7                          |
| BCD_1254            |         | 0               | 0                          |
| <NULL>              | <NULL>  | <NULL>          | 0                          |
+---------------------+---------+-----------------+----------------------------+

Обратите внимание, что в отличие от Oracle Database,
пустая строка ('') и NULL-строка в SQLite различаются (строки 3 и 4 в результате выборки).

Аналогичным образом можно использовать функции LTRIM и RTRIM:

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

WITH t AS (SELECT '123456789 % ABCDEFG' AS txt UNION ALL
           SELECT '123456789 % ABCcEFG' UNION ALL
           SELECT 'BCD_1254' UNION ALL
           SELECT NULL)
, tri AS (
SELECT txt
     , LTRIM(txt, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789') AS L_trm 
     , RTRIM(txt, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789') AS R_trm 
     ,  TRIM(txt, 'ABCDEFGHIJKLMNOPQRSTUVWXYZ_0123456789') AS trm 
FROM t)
--
SELECT 'TRIM' AS what, txt, trm, LENGTH(trm) AS len_trm, IFNULL(LENGTH(trm), 0) AS ifnull_trm FROM tri
UNION ALL
SELECT 'LTRIM', txt, L_trm, LENGTH(L_trm), IFNULL(LENGTH(L_trm), 0) FROM tri
UNION ALL 
SELECT 'RTRIM', txt, R_trm, LENGTH(R_trm), IFNULL(LENGTH(R_trm), 0) FROM tri;

+-------+---------------------+------------------+---------+------------+
| what  |         txt         |       trm        | len_trm | ifnull_trm |
+-------+---------------------+------------------+---------+------------+
| TRIM  | 123456789 % ABCDEFG |  %               | 3       | 3          |
| TRIM  | 123456789 % ABCcEFG |  % ABCc          | 7       | 7          |
| TRIM  | BCD_1254            |                  | 0       | 0          |
| TRIM  | <NULL>              | <NULL>           | <NULL>  | 0          |
| LTRIM | 123456789 % ABCDEFG |  % ABCDEFG       | 10      | 10         |
| LTRIM | 123456789 % ABCcEFG |  % ABCcEFG       | 10      | 10         |
| LTRIM | BCD_1254            |                  | 0       | 0          |
| LTRIM | <NULL>              | <NULL>           | <NULL>  | 0          |
| RTRIM | 123456789 % ABCDEFG | 123456789 %      | 12      | 12         |
| RTRIM | 123456789 % ABCcEFG | 123456789 % ABCc | 16      | 16         |
| RTRIM | BCD_1254            |                  | 0       | 0          |
| RTRIM | <NULL>              | <NULL>           | <NULL>  | 0          |
+-------+---------------------+------------------+---------+------------+

ORA600

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

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

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

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

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