searching blob or text
Posted in 1999
Edward Rosenthal could not run a wildcard LIKE search on a TEXT or BLOB column and got error 219. Keith Sharp expected LIKE to work on TEXT but not binary data. Art Kagel and Jonathan Leffler said SQL cannot filter TEXT or BYTE contents without a UDO or an IUS text-search datablade (likely needing a smart blob); otherwise fetch and search in the application. Edward worked around it by using LVARCHAR.
Topics: SQL Development & Query Writing, Jobs, Consulting & Announcements
I want to be able to do a wildcard search on a text field or blob. The select statement fails with a like '%%' expression. I have defined the field as text. I presume i would encounter it as a blob as well. Anybody?
The "like '%STRING%'" should work on a text column, but will most likely fail on a binary column. The data will be stored in binary encryption in a BLOB. Edward Rosenthal <erosenthal@mfi.com> wrote in article <37BA2687.9560A871@mfi.com>... > I want to be able to do a wildcard search on a text field or blob. > The select statement fails with a like '%%' expression. > I have defined the field as text. I presume i would encounter it as a > blob as well. > Anybody? >
I get error 219 - Keith Sharp wrote: > The "like '%STRING%'" should work on a text column, but will most likely > fail on a binary column. The data will be stored in binary encryption in a > BLOB. > > Edward Rosenthal <erosenthal@mfi.com> wrote in article > <37BA2687.9560A871@mfi.com>... > > I want to be able to do a wildcard search on a text field or blob. > > The select statement fails with a like '%%' expression. > > I have defined the field as text. I presume i would encounter it as a > > blob as well. > > Anybody? > >
Edward Rosenthal wrote: > > I want to be able to do a wildcard search on a text field or blob. > The select statement fails with a like '%%' expression. > I have defined the field as text. I presume i would encounter it as a > blob as well. > Anybody? Unless you have UDO you cannot filter on the contents of a TEXT or DATA BLOB column at all in SQL. You would have to fetch each BLOB into your application and search it manually. Art S. Kagel
Edward Rosenthal wrote: > I want to be able to do a wildcard search on a text field or blob. > The select statement fails with a like '%%' expression. > I have defined the field as text. I presume i would encounter it as a > blob as well. > Anybody? Nothing doing. You'd need to use an IUS text search datablade, and that would probably require a smart blob rather than TEXT or BYTE. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>
i discovered if i used lvarchar i could cover the problem... ( and my behind...) "Art S. Kagel" wrote: > Edward Rosenthal wrote: > > > > I want to be able to do a wildcard search on a text field or blob. > > The select statement fails with a like '%%' expression. > > I have defined the field as text. I presume i would encounter it as a > > blob as well. > > Anybody? > > Unless you have UDO you cannot filter on the contents of a TEXT or DATA > BLOB column at all in SQL. You would have to fetch each BLOB into your > application and search it manually. > > Art S. Kagel