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

Comments

• Bryan_H Vertica Employee Administrator

  April 2022

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

  1
• mosheg Vertica Employee Administrator

  April 2022

  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)
  

  1