How to search particular string in long varchar column?

SK21SK21 Vertica Customer
edited January 2021 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 - Select Field - Administrator
    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 - Select Field - Administrator
    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