ReSQL.ru
     
powered by simpleCommunicator - 2.0.53     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выборка всех подстрок и строки
11 сообщений из 11, страница 1 из 1
Выборка всех подстрок и строки
    #39860595
HotShot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HotShot
Участник
Добрый день!
Подскажите как выбрать из строки, хранящейся в столбце VARCHAR, все подстроки определенной длины (например 5), содержащие только латинские буквы и цифры. Подстроки могут разделяться пробелами. запятыми, точками с запятой.
Например:
исходная таблица
COLфыва ABR23 HY234; пров 2W56Tждл 3YYG5; пров 2W56 ячс

надо получить таблицу:
COL_RESABR23HY2342W56T3YYG52W56T

ORACLE 9, через REGEXP не получилось.
И можно ли это сделать через запрос, без использования хранимки?
...
Рейтинг: 0 / 0
11.09.2019, 22:29
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39860619
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Лови:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
create table tbl as select 'fiva ABR23 HY234; prov 2W56T' col from dual union all
select 'zdl 3YYG5; prov 2W56T cs' from dual
/
with t as (
           select ' ' || replace(
                                 translate(
                                           col,
                                           ';,',
                                           '  '
                                          ),
                                 ' ',
                                 '  '
                                ) || ' ' adjusted_col,
                  ' ' || replace(
                                 translate(
                                           col,
                                           '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ;,',
                                           'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  '
                                          ),
                                 ' ',
                                 '  '
                                ) || ' ' col_mask
                from  tbl
             )
select  substr(
               adjusted_col,
               instr(
                     col_mask,
                     ' XXXXX ',
                     1,
                     level
                    ) + 1,
               5
              ) col_res
  from  t
  connect by level <= (length(col_mask) - length(replace(col_mask,' XXXXX '))) / 7
         and rowid = prior rowid
         and prior sys_guid() is not null
/

COL_RES
-------
ABR23
HY234
2W56T
3YYG5
2W56T

SQL>



SY.
...
Рейтинг: 0 / 0
11.09.2019, 23:34
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39860633
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Почему-то решил заглавные латинские:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
SQL> drop table tbl purge
  2  /

Table dropped.

SQL> create table tbl as select 'fiva ABR23 HY234; prov 2W56T' col from dual union all
  2  select 'zdl 3YYG5; prov 2W56T cs' from dual
  3  /

Table created.

SQL> with t as (
  2             select ' ' || replace(
  3                                   translate(
  4                                             col,
  5                                             ';,',
  6                                             '  '
  7                                            ),
  8                                   ' ',
  9                                   '  '
 10                                  ) || ' ' adjusted_col,
 11                    ' ' || replace(
 12                                   translate(
 13                                             col,
 14                                             '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ;,',
 15                                             'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  '
 16                                            ),
 17                                   ' ',
 18                                   '  '
 19                                  ) || ' ' col_mask
 20                  from  tbl
 21               )
 22  select  substr(
 23                 adjusted_col,
 24                 instr(
 25                       col_mask,
 26                       ' XXXXX ',
 27                       1,
 28                       level
 29                      ) + 1,
 30                 5
 31                ) col_res
 32    from  t
 33    connect by level <= (length(col_mask) - length(replace(col_mask,' XXXXX '))) / 7
 34           and rowid = prior rowid
 35           and prior sys_guid() is not null
 36  /

COL_RES
-------
ABR23
HY234
2W56T
3YYG5
2W56T



SY.
...
Рейтинг: 0 / 0
12.09.2019, 00:13
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39860768
Да ну
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Да ну
Участник
Наверное можно и проще - разбить на слова по разделителям (пробел и запяточие) через connect by и отбросить лишнее:

Код: plsql
1.
2.
...
where length(word)=5 and ltrim(upper(word),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') is null
...
Рейтинг: 0 / 0
12.09.2019, 10:50
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39860972
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Да нуНаверное можно и проще - разбить на слова по разделителям (пробел и запяточие) через connect by и отбросить лишнее:


Можно, но уровней иерархии будет куда больше и посему будет менее эффективно:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
with t as (
           select ' ' || translate(
                                   col,
                                   ';,',
                                   '  '
                                  ) || ' ' adjusted_col
                from  tbl
             )
select  '[' || substr(
               adjusted_col,
               instr(adjusted_col,' ',1,level) + 1,
               instr(adjusted_col,' ',1,level + 1) - instr(adjusted_col,' ',1,level) - 1
              ) || ']' col_res,
        max(level) over(partition by rowid) max_level
  from  t
  where length(
               substr(
                      adjusted_col,
                      instr(adjusted_col,' ',1,level) + 1,
                      instr(adjusted_col,' ',1,level + 1) - instr(adjusted_col,' ',1,level) - 1
                     )
              ) = 5
    and ltrim(
              upper(
                    substr(
                           adjusted_col,
                           instr(adjusted_col,' ',1,level) + 1,
                           instr(adjusted_col,' ',1,level + 1) - instr(adjusted_col,' ',1,level) - 1
                          )
                   ),
              '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
             ) is null
  connect by instr(adjusted_col,' ',1,level) != 0
         and rowid = prior rowid
         and prior sys_guid() is not null
/

COL_RES  MAX_LEVEL
------- ----------
[ABR23]          6
[HY234]          6
[2W56T]          6
[3YYG5]          5
[2W56T]          5

SQL> with t as (
  2             select ' ' || replace(
  3                                   translate(
  4                                             col,
  5                                             ';,',
  6                                             '  '
  7                                            ),
  8                                   ' ',
  9                                   '  '
 10                                  ) || ' ' adjusted_col,
 11                    ' ' || replace(
 12                                   translate(
 13                                             col,
 14                                             '0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ;,',
 15                                             'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX  '
 16                                            ),
 17                                   ' ',
 18                                   '  '
 19                                  ) || ' ' col_mask
 20                  from  tbl
 21               )
 22  select  substr(
 23                 adjusted_col,
 24                 instr(
 25                       col_mask,
 26                       ' XXXXX ',
 27                       1,
 28                       level
 29                      ) + 1,
 30                 5
 31                ) col_res,
 32                max(level) over(partition by rowid) max_level
 33    from  t
 34    connect by level <= (length(col_mask) - length(replace(col_mask,' XXXXX '))) / 7
 35           and rowid = prior rowid
 36           and prior sys_guid() is not null
 37  /

COL_RES  MAX_LEVEL
------- ----------
ABR23            3
HY234            3
2W56T            3
3YYG5            2
2W56T            2

SQL>



Можно конечно прилепить замену последовательных разделителей на пробел но маской проще (IMHO).

SY.
...
Рейтинг: 0 / 0
12.09.2019, 14:33
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39860980
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Упс, MAX() OVER() выполнится после WHERE так-что реально:

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
SQL> with t as (
  2             select ' ' || translate(
  3                                     col,
  4                                     ';,',
  5                                     '  '
  6                                    ) || ' ' adjusted_col
  7                  from  tbl
  8               )
  9  select  level,'[' || substr(
 10                 adjusted_col,
 11                 instr(adjusted_col,' ',1,level) + 1,
 12                 instr(adjusted_col,' ',1,level + 1) - instr(adjusted_col,' ',1,level) - 1
 13                ) || ']' col_res
 14    from  t
 15    connect by instr(adjusted_col,' ',1,level) != 0
 16           and rowid = prior rowid
 17           and prior sys_guid() is not null
 18  /

     LEVEL COL_RES
---------- -------
         1 [fiva]
         2 [ABR23]
         3 [HY234]
         4 []
         5 [prov]
         6 [2W56T]
         7 []
         1 [zdl]
         2 [3YYG5]
         3 []
         4 [prov]
         5 [2W56T]
         6 [cs]
         7 []

14 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
12.09.2019, 14:40
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39861018
Да ну
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Да ну
Участник
До кучи:
Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
with q1 as (
  select 1 id,'fiva ABR23;HY234; prov 2W56T' col from dual union all
  select 2,   'zdl 3YYG5; prov 2W56T cs' from dual union all
  select 3,   ';aaaaa' from dual
),
q2 as (
  select id,replace(col,' ',';')||';' col from q1
),
q3 as (
  select 
    id, col, level lv,
    decode(level,1,1,instr(col, ';', 1, level-1)+1) pos1,
    instr(col,';',1,level) pos2
  from q2
  connect by  instr(col,';',1,level)>0
          and prior id = id
          and prior sys_guid() is not null
) 
select id,col,pos1,pos2,lv,substr(col,pos1,pos2-pos1) word
  from q3
 where pos2-pos1=5 
   and ltrim(upper(substr(col,pos1,pos2-pos1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') is null

IDCOLPOS1POS2LVWORD1fiva;ABR23;HY234;;prov;2W56T;6112ABR231fiva;ABR23;HY234;;prov;2W56T;12173HY2341fiva;ABR23;HY234;;prov;2W56T;242962W56T2zdl;3YYG5;;prov;2W56T;cs;51023YYG52zdl;3YYG5;;prov;2W56T;cs;172252W56T3;aaaaa;272aaaaa
...
Рейтинг: 0 / 0
12.09.2019, 15:21
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39861022
Фотография SY
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
SY
Участник
Да нуДо кучи

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
with q1 as (
  select 1 id,'fiva ABR23;HY234; prov 2W56T' col from dual union all
  select 2,   'zdl 3YYG5; prov 2W56T cs' from dual union all
  select 3,   ';aaaaa' from dual
),
q2 as (
  select id,replace(col,' ',';')||';' col from q1
),
q3 as (
  select
    id, col, level lv,
    decode(level,1,1,instr(col, ';', 1, level-1)+1) pos1,
    instr(col,';',1,level) pos2
  from q2
  connect by  instr(col,';',1,level)>0
          and prior id = id
          and prior sys_guid() is not null
)
select id,col,pos1,pos2,lv,substr(col,pos1,pos2-pos1) word
  from q3
-- where pos2-pos1=5
--   and ltrim(upper(substr(col,pos1,pos2-pos1)),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ') is null
/

        ID COL                                 POS1       POS2         LV WORD
---------- ----------------------------- ---------- ---------- ---------- ------
         1 fiva;ABR23;HY234;;prov;2W56T;          1          5          1 fiva
         1 fiva;ABR23;HY234;;prov;2W56T;          6         11          2 ABR23
         1 fiva;ABR23;HY234;;prov;2W56T;         12         17          3 HY234
         1 fiva;ABR23;HY234;;prov;2W56T;         18         18          4
         1 fiva;ABR23;HY234;;prov;2W56T;         19         23          5 prov
         1 fiva;ABR23;HY234;;prov;2W56T;         24         29          6 2W56T
         2 zdl;3YYG5;;prov;2W56T;cs;              1          4          1 zdl
         2 zdl;3YYG5;;prov;2W56T;cs;              5         10          2 3YYG5
         2 zdl;3YYG5;;prov;2W56T;cs;             11         11          3
         2 zdl;3YYG5;;prov;2W56T;cs;             12         16          4 prov
         2 zdl;3YYG5;;prov;2W56T;cs;             17         22          5 2W56T
         2 zdl;3YYG5;;prov;2W56T;cs;             23         25          6 cs
         3 ;aaaaa;                                1          1          1
         3 ;aaaaa;                                2          7          2 aaaaa

14 rows selected.

SQL>



SY.
...
Рейтинг: 0 / 0
12.09.2019, 15:35
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39861129
NoGot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NoGot
Участник
HotShotДобрый день!
Подскажите как выбрать из строки, хранящейся в столбце VARCHAR, все подстроки определенной длины (например 5), содержащие только латинские буквы и цифры. Подстроки могут разделяться пробелами. запятыми, точками с запятой.
Например:
исходная таблица
COLфыва ABR23 HY234; пров 2W56Tждл 3YYG5; пров 2W56 ячс

надо получить таблицу:
COL_RESABR23HY2342W56T3YYG52W56T

ORACLE 9, через REGEXP не получилось.
И можно ли это сделать через запрос, без использования хранимки?

А почему не получилось через regexp?

Код: plsql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
with q1 as (
  select 1 id,'fiva ABR23;HY234; prov 2W56T' col from dual union all
  select 2,   'zdl 3YYG5; prov 2W56T cs' from dual union all
  select 3,   ';aaaaa' from dual
), q2 as (
select
  id, col, rn, pattern
, regexp_substr(col, pattern, 1, rn, null, 1) as res_col
  from q1
 cross join (
   select
     rownum as rn
   , '([a-zA-Z1-9]{5})' as pattern
     from dual
  connect by rownum <= (select max(length(col))/5 from q1)
 )
)
select res_col
  from q2
 where res_col is not null
...
Рейтинг: 0 / 0
12.09.2019, 18:04
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39861139
NoGot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
NoGot
Участник
Я все понял, извините
...
Рейтинг: 0 / 0
12.09.2019, 18:34
| Ответить | Цитировать | Написать  
Выборка всех подстрок и строки
    #39861188
HotShot
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
HotShot
Участник
авторА почему не получилось через regexp?
ORACLE 9
Спасибо! Все получилось вроде.
...
Рейтинг: 0 / 0
12.09.2019, 21:53
| Ответить | Цитировать | Написать  
11 сообщений из 11, страница 1 из 1
Форумы / Oracle [игнор отключен] [закрыт для гостей] / Выборка всех подстрок и строки
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=52&msg=39861139&tid=1882089]:
 0ms
get settings:
 9ms
get forum list:
 13ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 28ms
get topic data:
 9ms
get forum data:
 3ms
get page messages:
 44ms
get tp. blocked users:
 1ms
others: 13ms
total:  128ms
созд. / загр.: 128ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]