Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!

How to search particular string in long varchar column?

SK21SK21
edited January 7 in General Discussion

SELECT query ,user_name FROM analytics.query_tracker where REGEXP_LIKE(query, 'junk');
(Also long datatype does not support simple like and ilike)
This query is returning error-
[Code: 4551, SQL State: 22021] [Vertica]VJDBC ERROR: Regexp encountered an invalid UTF-8 character

Best Answers

  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    REGEXP_LIKE works fine on LONG data types like LONG VARCHAR.

    Example:

    dbadmin=> CREATE TABLE t (c LONG VARCHAR);
    CREATE TABLE
    
    dbadmin=> INSERT INTO t SELECT 'Some junk';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM t WHERE REGEXP_LIKE(c, 'junk');
         c
    -----------
     Some junk
    (1 row)
    

    The error you are getting is because you've stored some non UTF-8 data in the column in some record(s).

    Take a look here:
    https://forum.vertica.com/discussion/240991/remove-non-utf-8-characters-from-varchar-column

  • Jim_KnicelyJim_Knicely Administrator
    Accepted Answer

    Like this:

    dbadmin=> INSERT INTO t SELECT E'\xa0' || 'Test!' || E'\xa0';
     OUTPUT
    --------
          1
    (1 row)
    
    dbadmin=> SELECT * FROM t;
         c
    -----------
     Some junk
     ▒Test!▒
    (2 rows)
    
    dbadmin=> SELECT * FROM t WHERE REGEXP_LIKE(c, '[^\t\r\n\x20-\x7E]+', 'b');
        c
    ---------
     ▒Test!▒
    (1 row)
    

    But, you can probably just run your original query with the B option of the REGEXP_LIKE function...

    SELECT query, user_name FROM analytics.query_tracker where REGEXP_LIKE(query, 'junk', 'b');

Answers

Leave a Comment

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

Can't find what you're looking for? Search the Vertica Documentation, Knowledge Base, or Blog for more information.