I would like to know whether "CONTAINS" function will work

[gvkiranreddy]

Hi,

I would like to know whether "CONTAINS" function will work in vertica or not?

Let me go through in detail, here i have CLOB column in vertica, for doing text search at QLIKVIEW level i wanted to go with CONTAINS clause, so can anyone confirm me whether this function is avail in vertica or not. please do the needful.

for example:

CREATE TABLE accumtbl
(id NUMBER, text VARCHAR2(4000) );

INSERT INTO accumtbl VALUES
( 1, 'the little dog played with the big dog
while the other dog ate the dog food');
INSERT INTO accumtbl values
(2, 'the cat played with the dog');

CREATE INDEX
accumtbl_idx ON accumtbl (text)
indextype is ctxsys.context;

PROMPT dog ACCUM cat

SELECT
SCORE(10)
FROM
accumtbl
WHERE
CONTAINS (text, 'dog ACCUM cat', 10) > 0;

This is avail in the ORACLE db, so i would like to know the same clause or function is avail or not in vertica db.

Thanks & Regards,
Kiran

[JimKnicely]

Hello,

There is not a CLOB data type in Vertica. You can store text up to 32,000 bytes in a VARCHAR field.

Vertica does not have a CONTAINS function.

You might be able work with regular expressions to try and score your searches.

For example:

Code: Select all

dbadmin=> select id, text, regexp_count(text, 'dog.+?cat') from accumtbl order by 3 desc;
 id |                                    text                                     | regexp_count 
----+-----------------------------------------------------------------------------+--------------
  3 | the dog smelled the cat after the dog kissed the cat                        |            2
  4 | the dog slept next the cat                                                  |            1
  1 | the little dog played with the big dog while the other dog ate the dog food |            0
  2 | the cat played with the dog                                                 |            0
(4 rows)

Thanks!

[gvkiranreddy]

Hi Jim,

Thanks for your reply with detailed example, i also tried in vertica DB, it was working fine and giving same result as you were shown, in that i didn't understand about 'dog.+?cat' this one, in this, can you please explain me about the symbols, why these are used? Necessity of these symbols? if we miss anyone of these symbol(' . ', ' +', ' ? ') it's giving output as zero, can you please give me the much more detail.

select id, text, regexp_count(text, 'dog.+?cat') from accumtbl order by 3 desc;

i tried with multiple instances like:
1. select id, text, regexp_count(text, 'dog.+?cat') from accumtbl order by 3 desc;
2. select id, text, regexp_count(text, 'dog+?cat') from accumtbl order by 3 desc;
3. select id, text, regexp_count(text, 'dog.+cat') from accumtbl order by 3 desc;
4. select id, text, regexp_count(text, 'dog+cat') from accumtbl order by 3 desc;

I would like use contains function in vertica to do text search, i got to know it is not avail in vertica, there is any alternative instead of this one? if any can you please let me know? I would like search like below:

for example:

SELECT ID, TEXT FROM ABC.SRVC_TKT WHERE TEXT=('LAPPEE' AND 'CHARGER' OR 'WIRE' |'BATTERY' AND 'BAG'|'ANY' AND 'ACCESSORIES')'

[JimKnicely]

Hi,

The ".+?" symbols are used in regular expression as a wild cart. I used them to find all instances of text starting with "dog" following by any other text and ending with "cat". It's like the "*" wild cart... You can not change the order of the symbols if what you want is the simple wild cart example of "dog*cat".

If you do a google search on "regular expressions" and you'll find a bunch of examples!

For your example query, in Vertica you might be able to write it using the LIKE operator. Something like this:

Code: Select all

SELECT ID, TEXT FROM SRVC_TKT
 WHERE (TEXT LIKE '%LAPPEE%' AND TEXT LIKE '%CHARGER%') OR
       ((TEXT LIKE '%WIRE%' OR TEXT LIKE '%BATTERY%') AND ((TEXT LIKE '%BAG%' OR TEXT LIKE '%ANY%') AND
       TEXT LIKE '%ACCESSORIES%'));

But that isn't the best solution 'cause it'll find words that include the text, like BAGGAGE, ALBANY, WIRELESS, etc...

If you want to search for keywords than use regular expressions and the Vertica REGEXP_LIKE function.

Maybe something crazy like this?

Code: Select all

SELECT ID, TEXT FROM SRVC_TKT WHERE REGEXP_LIKE (TEXT, '((\bLAPPEE\b.+\bCHARGER\b)|(\bCHARGER\b.+\bLAPPEE\b))|((\bWIRE\b|\bBATTERY\b).+((\bBAG\b|\bANY\b).+\bACCESSORIES\b)|(\bACCESSORIES\b.+(\bBAG\b|\bANY\b))|((\bBAG\b|\bANY\b).+\bACCESSORIES\b)|(\bACCESSORIES\b.+(\bBAG\b|\bANY\b)).+(\bWIRE\b|\bBATTERY\b))');

Example:

Code: Select all

dbadmin=> SELECT * FROM SRVC_TKT ORDER BY 1;
 id |                  text
----+----------------------------------------
  1 | LAPPEE DOG CHARGER
  2 | LAPPEE DOG CHARGE WIRE BAG
  3 | LAPPEE DOG CHARGE WIRE BAG ACCESSORIES
  4 | LAPPEE DOG CHARGE WIRE ANY ACCESSORIES
  5 | LAPPEE DOG CHARGE WIRE ACCESSORIES
  6 | BATTERY
  7 | LAPPEES
(7 rows)

dbadmin=> SELECT ID, TEXT FROM SRVC_TKT WHERE REGEXP_LIKE (TEXT, '((\bLAPPEE\b.+\bCHARGER\b)|(\bCHARGER\b.+\bLAPPEE\b))|((\bWIRE\b|\bBATTERY\b).+((\bBAG\b|\bANY\b).+\bACCESSORIES\b)|(\bACCESSORIES\b.+(\bBAG\b|\bANY\b))|((\bBAG\b|\bANY\b).+\bACCESSORIES\b)|(\bACCESSORIES\b.+(\bBAG\b|\bANY\b)).+(\bWIRE\b|\bBATTERY\b))')
dbadmin-> ORDER BY 1;
 ID |                  TEXT
----+----------------------------------------
  1 | LAPPEE DOG CHARGER
  3 | LAPPEE DOG CHARGE WIRE BAG ACCESSORIES
  4 | LAPPEE DOG CHARGE WIRE ANY ACCESSORIES
(3 rows)