If the sql text is saved in a table how to search for particular text using like operator

vinutauro

I want a query to search a SQL text from the v$sqlstats table from oracle stored in Vertica . trying the below query but it's returning blank rows. Could you please help?

with test_sql_text as (select ' SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE(''2022-01-01 17:18:10'',''yyyy-mm-dd hh24:mi:ss'') ' sql_text from dual)

SELECT *
FROM
test_sql_text
WHERE
REGEXP_LIKE (trim(UPPER(SQL_TEXT)) ,
trim(UPPER
(' SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE(''2022-01-01 17:18:10'',''yyyy-mm-dd hh24:mi:ss'') ')),'n');

Bryan_H

Do you need a regex search? The following works, and you can also use SQL wildcard '%' to match:
with test_sql_text as (select ' SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE(''2022-01-01 17:18:10'',''yyyy-mm-dd hh24:mi:ss'') ' sql_text from dual)

SELECT *
FROM
test_sql_text
WHERE
trim(UPPER(SQL_TEXT))
ILIKE
trim(UPPER
(' SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE(''2022-01-01 17:18:10'',''yyyy-mm-dd hh24:mi:ss'') '));

mosheg

In addition, Standard conforming strings must be ON to use Unicode-style string literals (U&'\nnnn').
For example:

SET ESCAPE_STRING_WARNING TO OFF; 
-- Do not issue a warning when a backslash is used in a string literal during the current session

SET STANDARD_CONFORMING_STRINGS TO OFF; 
-- To support SQL:2008 string literals within Unicode escapes and to use Unicode-style string literals (U&'\nnnn').

with test_sql_text as
(select 'SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE(\'2022-01-01 17:18:10\',\'yyyy-mm-dd hh24:mi:ss\')' as SQL_TEXT)
select SQL_TEXT from test_sql_text;


                                                SQL_TEXT
--------------------------------------------------------------------------------------------------------
 SELECT * FROM CE_EVENT_PRSNL WHERE UPDT_DT_TM > TO_DATE('2022-01-01 17:18:10','yyyy-mm-dd hh24:mi:ss')
(1 row)