Search begin of word

Classic Servoy
1

Want to search for a string in a text, this string has to appear as the beginning of a word. So when searching for ‘hello’ I don’t want to find fields containing ‘othello’.

With sybase this can be done:

foundset.find()
foundset.textfield = 'hello%'
foundset.newRecord()
foundset.textfield = '%[^a-zA-Z]hello%'
foundset.search()

But in postgresql this does not work. How can this be achieved in postgresql (database vendor independent)? We cannot force servoy to use a regular expression match (~ in stead of LIKE), can we?

2

Hi Michel,

PostgreSQL has support for regex searches but I doubt Servoy or the JDBC driver uses the same syntax as Sybase does. But perhaps Servoy can answer that one.
Also regex searches are not in the SQL standard I believe so to make it database vendor independent code for this might require some more work on your end.

For more info on Pg pattern matching check out the excellent manual of PostgreSQL.
http://www.postgresql.org/docs/8.4/stat … ching.html

Hope this helps.

3

Hello Harjo,

Thanks for the reply. PostgreSQL is indeed much more powerfull with the support for regex but there are much less possibilities in the patterns that can be used with LIKE compared to sybase, ms sql or mysql.

So searching for the beginning of a word in a text-field using a sevoy find()/search() does not seem to be possible. Or does someone has an idea how this can be done?

4

Hi Michel,

michel:
Hello Harjo,

You can call me Robert ;)

You can use ‘hello%’ on every sql vendor. It’s the regex part that is not that portable.
So yes, you can search for beginning of a word using that syntax on PostgreSQL and any other vendor.
Or even better use ‘#hello%’ to make it case-insensitive.

Hope this helps.

5

:oops: Haha, must have been reading too much postings, sorry Robert…

But what I meant is that it is not possible to find hello as the beginning of a word in a text-field.

A field containing ‘please say hello othello!!!’ would show up in the result and a field containing ‘please say hi othello!!!’ would not

6

Hi Michel! ;-)

7

Hello Harjo, I knew there was a Harjo somewhere… :D