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');

Comments

  • Bryan_HBryan_H Vertica Employee Administrator

    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'') '));

  • moshegmosheg Vertica Employee Administrator

    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)
    

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file