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
Soundex(!) and Near(;) operator in Vertica DB
Moderator: NorbertKrupa
-
- Newbie
- Posts: 9
- Joined: Tue May 21, 2013 7:09 am
Soundex(!) and Near(;) operator in Vertica DB
Post by gvkiranreddy » Wed Jul 17, 2013 1:39 pm
- Attachments
-
- Text Search in Vertica.xlsx
- (10.6 KiB) Downloaded 564 times
Return to “Vertica Database Development”
Jump to
- General
- ↳ Welcome to vertica-forums.com!
- ↳ Board Announcements
- ↳ Request for New Forums Categories
- ↳ Announcements, Events and Activities
- ↳ Vertica Links
- ↳ General
- ↳ Employment
- ↳ Vertica Certification
- Newbie
- ↳ New to Vertica
- ↳ New to Vertica Database Administration
- ↳ New to Vertica Database Development
- ↳ New to Vertica SQL
- Using Vertica
- ↳ Vertica Database Administration
- ↳ Vertica Database Development
- ↳ Vertica SQL
- ↳ Vertica SQL Functions
- ↳ Vertica Database Designer (DBD)
- ↳ Vertica User Defined Functions (UDFs)
- ↳ Vertica External Procedures
- ↳ Vertica Analytics
- ↳ Vertica Management Console
- ↳ Vertica Error Codes
- ↳ Vertica Backup & Recovery
- ↳ Vertica Installation
- ↳ Vertica Security
- ↳ Vertica Performance Tuning
- ↳ Vertica Administration Tools
- ↳ Vertica Upgrade
- ↳ Vertica Migration
- ↳ Vertica and the Operating System
- ↳ Vertica Data Load
- ↳ Vertica Tips, Lessons and Examples
- ↳ Vertica "How to..."
- Connecting to Vertica
- ↳ ADO.NET
- ↳ Cognos
- ↳ DBeaver
- ↳ dbVisualizer
- ↳ HDFS Connector
- ↳ Hadoop Connector
- ↳ HCatalog Connector
- ↳ Informatica PowerCenter
- ↳ JDBC
- ↳ Microsoft SQL Server Analysis Services (SSAS)
- ↳ MicroStrategy
- ↳ Microsoft SQL Server Integration Services (SSIS)
- ↳ ODBC
- ↳ Perl
- ↳ PHP
- ↳ Python
- ↳ R Language Integration
- ↳ Rhapsody Interface Engine
- ↳ SQL Workbench/J
- ↳ SQuirreL SQL Client
- ↳ Talend
- ↳ Tableau
- ↳ Toad for Data Analysts
- ↳ Vertica SDK API
- ↳ VoltDB
- ↳ vSQL
- Vertica Portfolio
- ↳ Vertica Premium Edition
- ↳ Vertica Express Edition
- ↳ Vertica Community Edition
- ↳ Vertica SQL on Hadoop
- ↳ Vertica in the AWS Cloud
- ↳ Vertica on Microsoft Azure
- All times are UTC+01:00
- Top
- Delete all board cookies
Powered by phpBB ® | phpBB3 theme by KomiDesign