Soundex(!) and Near(;) operator in Vertica DB

[gvkiranreddy]

Hi Nnani/ Knicely87

Thanks for your earlier posts:)

I am doing text search in qlikview by using vertica DB, so in that case i need whether these operators will work in vertica DB or not, for that i did a POC, and listed all below, which are executed and which are not.
Logical AND/& ---Tried/ working with other alternate.

Logical OR/ | --- Tried/ working fine, but not with Logical OR.

Logical NOT/ ~ --- Need assistance

Near NEAR/ ; --- Need assistance

Soundex ! --- Need assistance

Wildcard
(Single) _(Underscore)---Tried and working fine as expected.

Wildcard
(Multiple) % ---Tried and working fine as expected.

Stem $ --- Need assistance( May be it'll work)

Weight * ---Tried and working fine as expected.

Escape
Characters { }, \ ---Tried and working fine as expected.

Grouping
Characters (), [] ---Tried and working fine as expected.

Here i got struck by doing search text with SOUNDEX and NEAR operator in vertica DB, i tried with multiple examples by using REGEXP_LIKE to achieve the same for SOUNDEX and NEAR which is already avail in oracle DB, but that similar function is not working in vertica DB, Can you please tell me any solution to achieve this:

I tried to search like this way:
1. Logical OR:
eg1: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE(pm_name, '(V) OR (N)')
Result: not working with Logical OR

eg2: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE(pm_name, '(V)|(N)')
Result: Working fine and giving result as expected.

2. Logical AND:

eg1: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE(pm_name, '(V) AND (N)')
Result: not working with Logical AND/ & these two, instead of that i tried with another example with * . *(Star dot Star), in that case it is working fine, considering as AND. and the symbol(&) is not working in vertcia.

3. Logical NOT:
eg1: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE(pm_name, '(V) AND (N)')
Result: Especially to achieve this result i tried with multiple examples but am not getting the result which is expected.
eg2: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE(pm_name,'?:[V]')
eg3: SELECT * FROM SCHEMA.TEXT_SEARCH WHERE REGEXP_LIKE( col1, '[^[:alpha:]]' )
eg4: SELECT pm_id, pm_name, case when REGEXP_LIKE( pm_name, '[^(SSORIES )]')
THEN 'YES'
ELSE 'OUT'
END AS RESULT
FROM SCHEMA.TEXT_SEARCH

Here above mentioned example sequels are not giving correct result, as we expected, i think you feel little difficult in the attached document i can provide the all sequel's which are i tested for it. Kindly do the needful.

Thanks & Regards,
Kiran Reddy Gundala