Full text search support?

Does Omnisci support full text search like using contains in SQL to search whether a token exists in a column. And is there any way to provide the size information about the query output?

1 Like

Hi @Youan_Lu,

We currently support a limited number of strings functions; you can find them here

https://docs.omnisci.com/sql/data-manipulation-dml/functions-operators

The string fields are limited to a length of 32767 bytes and you can find a token in a fields but currently we haven’t a feature that scan all text fields (or something that scan an index build with string values of a table for that matter.

So if you want to search for a precise word in a field you can use the LIKE/ILIKE command (I guess you can use both encoded dictionary or encoded none fields) or REGEXP/REGEXP_LIKE as a example:

SELECT col1, col2, coln FROM table WHERE colt1 LIKE ‘% word %’ or colt1 LIKE ‘% word %’ ;

Take in account that our database is optimized for analytical workloads; it’s quite fast filtering data but on large projections the performance isn’t the best.

What do you mean with size? Getting the length of the output or limit the number of rows?

To limit the number of the rows that a query return you can use the LIMIT/OFFSET statements at the end of the query.

SELECT col1, col2, coln FROM table WHERE colt1 LIKE '% word %'  or colt1 LIKE '% word %'

LIMIT 100

Hope’s this helps

1 Like

Thank you! These information help a lot!

1 Like