Re: Any chance to surpass 4000 character limit?
Date: Mon, 26 Jan 2009 07:34:04 -0800 (PST)
Message-ID: <6d825ec9-fd1e-40f4-be18-1753b79beb1c_at_g39g2000pri.googlegroups.com>
On Jan 26, 5:27 pm, stegem..._at_naviga.de wrote:
> On 26 Jan., 13:11, "Vladimir M. Zakharychev"
>
> <vladimir.zakharyc..._at_gmail.com> wrote:
>
> ...
>
> > CLOB and an Oracle Text index over it should do the trick. You will
> > need to change the way you *search* that column though (use CONTAINS()
> > operator instead of LIKE/REGEXP_LIKE,) so this might not be an option
> > for you.
>
> Hi Vladimir,
> thanks for that idea, I have to check this. Our applications are
> connecting to several DBMS depending on customer preferences, besides
> Oracle we also work with MS SQL Server and MySQL. Both of those
> support longer character columns. We are using ODBC to connect to the
> database and of course we prefer to use the same generated SQL
> statements when dealing with any DBMS, but on the other hand sometimes
> we have to deal with DBMS differences. This might be such a case.
> With such an indexed CLOB, would it be possible to search for any
> string within the column or just for whole words? I. e. could I use
> the Contains() clause just in the same way as a LIKE '%..%' clause? We
> need to search any sort of character sequence within such a text, for
> example part of a name.
>
> > By the way, the limit is 4000 BYTES, not CHARACTERS (when working with
> > single-byte charsets you might never feel the difference, but as soon
> > as you go international you will almost immediately bump into it.)
>
> Thanks for pointing this out, but fortunately this will never be an
> issue for us, because our apps are created exclusively for our
> national market.
> Cheers
> Kay-Viktor
Well, Oracle Text gives a lot more than LIKE, but you can, of course, use it the same way as you use LIKE. Consider these examples:
SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, 'word') > 0 looks for "word", exactly
SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '%{or}%') > 0 looks for "or" substring (since "or" itself is a keyword in Text queries, we need to enclose it with curly braces here.)
SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, 'about(word)') > 0 Theme search: looks for texts about "word", but not necessarily having the word itself.
SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '$word') > 0 Stemmed search: looks for "word" and other terms with the same linguistic root (like "words.")
SELECT id FROM mytable WHERE CONTAINS(mytextcolumn, '?word') > 0 Fuzzy search: looks for "word" and all words spelled similarly.
Oracle Text Reference is a must-read if you want to play with it.
Regards,
Vladimir M. Zakharychev
N-Networks, makers of Dynamic PSP(tm)
http://www.dynamicpsp.com
Received on Mon Jan 26 2009 - 09:34:04 CST