Я пытаюсь получить данные из большого двоичного объекта, используя regexp_substr. Вот как выглядит капля
"select a,b ,upper(c from xyz), d from t1 where xyx;"
Требуемый результат:
t1
Вместо where в BLOB-объекте это может быть fetch, order by или group by.
Я пытаюсь с
REGEXP_SUBSTR(query_blob,'FROM\s(.*?)\s(WHERE|FETCH|ORDER|GROUP)',1,1,'i',1)
но это дает мне результат как
xyz), d from t1
Может ли кто-нибудь помочь мне получить имя таблицы между from и (где или получить, или сгруппировать, или упорядочить).
🤔 А знаете ли вы, что...
SQL поддерживает создание хранимых процедур и функций, которые могут быть вызваны из SQL-запросов.
Если предположить, что это простой запрос Oracle SQL (т. е. без вложенных/коррелированных подзапросов), вы можете использовать регулярное выражение ([a-z][a-z0-9_$#]*|"[^"]+") только для сопоставления символов в идентификаторе без кавычек или в кавычках (если вы используете другие языки, которые используют другие идентификаторы в кавычках, например квадратные скобки или обратные кавычки, вы также можете добавить их) на основе правил именования объектов базы данных:
Идентификаторы без кавычек должны начинаться с буквенного символа из набора символов вашей базы данных. Идентификаторы в кавычках могут начинаться с любого символа.
Идентификаторы без кавычек могут содержать только буквенно-цифровые символы из набора символов вашей базы данных, а также знак подчеркивания (_), знак доллара ($) и знак решетки (#). Ссылки на базы данных также могут содержать точки (.) и знаки «at» (@). Oracle настоятельно не рекомендует использовать $ и # в идентификаторах без кавычек.
Идентификаторы в кавычках могут содержать любые символы и знаки препинания, а также пробелы. Однако ни идентификаторы в кавычках, ни идентификаторы без кавычек не могут содержать двойные кавычки или нулевой символ (\0).
И следуйте за ним проверкой дополнительных предложений фильтра/агрегирования (также может быть псевдоним [которому в других диалектах SQL, отличных от Oracle, может предшествовать ключевое слово AS] и/или предложение HAVING без предложения GROUP BY или без фильтров) .
Так:
SELECT REGEXP_SUBSTR(
query_blob,
'FROM\s+([a-z][a-z0-9_$#]*|"[^"]+")' -- FROM clause
|| '(\s+([a-z][a-z0-9_$#]*|"[^"]+"))?' -- FROM Alias
|| '\s*(\s(WHERE|FETCH|ORDER|GROUP|HAVING)|$)', -- Filter/Aggregation Clauses
1,
1,
'i',
1
) AS frm
FROM table_name;
Что для вашего примера данных:
CREATE TABLE table_name (query_blob) AS
SELECT 'select a,b ,upper(c from xyz), d from t1 where xyx;' FROM DUAL
Выходы:
Если вы хотите еще больше усложнить выражение, то:
SELECT REGEXP_SUBSTR(
query_blob,
'FROM\s+' -- FROM clause
|| '('
|| '([a-z][a-z0-9_$#]*|"[^"]+")' -- Identifier
|| '(\(\s*' -- Start Function call
|| '(' -- Start optional group
|| '[a-z][a-z0-9_$#]*' -- Unquoted identifier
|| '|"[^"]+"' -- Or quoted identifier
|| '|''([^'']|'''')*''' -- Or string literal
|| ')?\s*' -- End optional group
|| '(' -- Start zero-or-more group
|| ',\s*[a-z][a-z0-9_$#]*' -- Unquoted identifier
|| '|,\s*"[^"]+"' -- Or quoted identifier
|| '|,\s*''([^'']|'''')*''' -- Or string literal
|| ')*' -- End zero-or-more group
|| '\s*\))?' -- End Function call
|| ')'
|| '(\s+([a-z][a-z0-9_$#]*|"[^"]+"))?' -- FROM Alias
|| '\s*(\s(WHERE|FETCH|ORDER|GROUP|HAVING)|$)', -- Filter/Aggregation Clauses
1,
1,
'i',
1
) AS frm
FROM table_name;
Что для примера данных:
CREATE TABLE table_name (query_blob) AS
SELECT 'select a,b ,upper(c from xyz), d from t1 where xyx;' FROM DUAL UNION ALL
SELECT 'select a,b ,upper(c from xyz), d from abc(''xyz'') where xyx;' FROM DUAL UNION ALL
SELECT 'select a,b ,upper(c from xyz), d from abc(def) where xyx;' FROM DUAL UNION ALL
SELECT 'select a,b ,upper(c from xyz), d from abc( def, ''ghi'', jkl ) where xyx;' FROM DUAL
Выходы:
Или, если вам нужно простое выражение, которое может быть решением на 90%:
SELECT REGEXP_SUBSTR(
query_blob,
'FROM\s+' -- FROM clause
|| '(\S+)' -- Identifier
|| '(\s+(\S+))?' -- FROM Alias
|| '\s*(\s(WHERE|FETCH|ORDER|GROUP|HAVING)|$)', -- Filter/Aggregation Clauses
1,
1,
'i',
1
) AS frm
FROM table_name;
Что для тех же данных примера соответствует первым трем строкам, но не последней (поскольку внутри скобок вызова функции есть пробелы).
Поскольку вы ищете текст между последними вхождениями предопределенных слов, если вы хотите сделать это без использования регулярного выражения, вот вариант, использующий только стандартные текстовые функции:
WITH -- cte acting as your sample table data
texts (ID, TEXT) AS
( Select 1, '"select a,b ,upper(c from xyz), d from t1 where xyx;"' From Dual Union All
Select 2, '"select a,b,d,e from t22 fetch last one;"' From Dual Union All
Select 3, '"select a,upper(c from xyz fetch 1 or 2 from tbl), d from t333 order by a;"' From Dual Union All
Select 4, '"select a,b ,upper(c from xyz group by c), d from t4t5t6 group by x,y;"' From Dual
),
... вы можете создать cte для определения комбинаций слов-рамок
-- cte defining border words - notice space character after word 1 and before word 2
words (WORD_1, WORD_2) AS
( Select 'FROM ', ' WHERE' From Dual Union All
Select 'FROM ', ' FETCH' From Dual Union All
Select 'FROM ', ' ORDER BY' From Dual Union All
Select 'FROM ', ' GROUP BY' From Dual
),
... еще один вызов, чтобы получить последние позиции слов-рамок в тексте
-- cte geting last position of words
last_occurrences AS
( Select t.ID, w.WORD_1, w.WORD_2,
Length(t.TEXT) - InStr(Upper(REVERSE(t.TEXT)), REVERSE(w.WORD_1)) + 1 "P1",
InStr(Upper(t.TEXT), w.WORD_2, Length(t.TEXT) - InStr(Upper(REVERSE(t.TEXT)), REVERSE(w.WORD_1)) + 1, 1) "P2"
From texts t
Inner Join words w ON( InStr(Upper(t.TEXT), w.WORD_1) > 0 And
InStr(Upper(t.TEXT), w.WORD_1) > 0
)
Where InStr(Upper(t.TEXT), w.WORD_2, Length(t.TEXT) - InStr(Upper(REVERSE(t.TEXT)), REVERSE(w.WORD_1)) + 1, 1) - 1 > 0
)
... используйте позиции, чтобы получить подстроку...
-- M a i n S Q L :
Select t.ID, t.TEXT,
SubStr( t.TEXT, o.P1, o.P2 - o.P1 ) "TBL"
From texts t
Inner Join last_occurrences o ON(o.ID = t.ID)
/* R e s u l t :
ID TEXT TBL
---------- --------------------------------------------------------------------------- ------------------
4 "select a,b ,upper(c from xyz group by c), d from t4t5t6 group by x,y;" t4t5t6
3 "select a,upper(c from xyz fetch 1 or 2 from tbl), d from t333 order by a;" t333
2 "select a,b,d,e from t22 fetch last one;" t22
1 "select a,b ,upper(c from xyz), d from t1 where xyx;" t1 */