Hello,
Let me start by saying that I'm very impressed with H2, it's features
and performance. I 'm using it for a new project (software implemented
in Scala & Java).
Currently I'm trying to figure out whether it's possible to query a
database table with and ARRAY column to find all records where array
contains a given element.
Something like this:
===============
drop table if exists arr;
create table arr (id identity, arcol array);
insert into arr values (1,('a','b')), (2, ('b','c')), (3,
('a','c','d'));
--And run a query that would give me all records that contain array
element 'b'
select * from arr a where 'b' in (a.arcol);
-- should return records 1 and 2.
================
Also, ideally it should be able to use an index so that I can search a
table with up to 1million rows super-quickly.
-----
To give some background: one of my columns will be a text string
(varchar), which I'm planning to split by space chars and put the
words in a separate computed column of type ARRAY. I'd like to be able
to search for records that have a specific word in computed column's
array , and looking to do it super-fast :) Oh, and this search by
array elements will actually be inside a "not exists() " clause of
another query, to make it more interesting.
Currently i'm doing it with locate() function :
select * from abc where ...... and not exists( select * from blah
locate(' '||abc.somecolumn||' ', ' '||blah.blahcolumn' ') <> 0);
I would really appreciate any hints or alternative approaches to solve
this problem.
Thank you !
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
