INSTR() - nth appearance of searchString

Dani

unread,

Feb 21, 2012, 7:54:00 AM2/21/12

to H2 Database

Hey everybody,

can anyone provide an approach to extract the nth appearance of a
search string within a string!?

e.g: instr('test H2 database test', 'e', 1, 2) would return 16; the
second occurrence of 'e'

H2: INSTR(string, searchString, [, startInt])
possible parameter: [, nth_appearance] of searchString

see also occurance: http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_functions_1103.htm

Dani

Noel Grandin

unread,

Feb 21, 2012, 8:36:09 AM2/21/12

to h2-da...@googlegroups.com, Dani

H2 doesn't implement INSTR the same way as Oracle.

You'd need to implement a FUNCTION ALIAS and write your own method in Java.
http://www.h2database.com/html/grammar.html#create_alias

Dani

unread,

Feb 21, 2012, 11:50:55 AM2/21/12

to H2 Database

OK thanks Noel,

CREATE ALIAS seems to be something I need to get familiar with...
http://groups.google.com/group/h2-database/browse_thread/thread/0ea0fa6ef1c7e553/c35ac2e379d04eda#c35ac2e379d04eda

I am not a Java programmer really. I am using a Visual Basic dialect
that compiles to Java bytecode!
www.jabaco.org
so implementing these kind of functions takes some adjusting on my
side :-)
I'll manage though...

Dani

Reply all

Reply to author

Forward