If the sql text is saved in a table how to search for particular text using like operator
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');
1
Comments
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'') '));
In addition, Standard conforming strings must be ON to use Unicode-style string literals (U&'\nnnn').
For example: