How to use SQL „LIKE“ on a GlobalStream where the Attribute Datatype is a LONGVARCHAR

Primary tabs

SQL, Caché

Hi all,

i‘m traying to run a sql LIKE Query on an attribute datatype LONGVARCHAR.

I also try allready with the SUBSTRING(Attribute,1,...), when i run the query, the resultat is empty without errors.

The Query:

 

select text from table

where text like ‘%abc%‘

 

many thx for help

BR

Darko

Replies

Hi Darko,

LONGVARCHAR is actually storing the text as a stream, so to make LIKE work, you'll need to convert it to a string, e.g. using SUBSTRING(), in the expression you're serving to the LIKE operator. The following works fine for me:

SELECT SUBSTRING(text, 1, 999999) FROM table WHERE SUBSTRING(text, 1, 9999999) LIKE '%abc%'

This looks a little clumsy, but streams are really meant for huge data that wouldn't fit in a VARCHAR. Note that you can get a massive amount of text in VARCHAR (%String) columns these days, so maybe that's worth considering.

Separately, when working with iFind, that'll provide faster search results and it also transparently takes care of the stream aspect.

Hi Benjamin,

thank for the fast answer,  but when i run the query with

select SUBSTRING(Bezeichnung, 1,9999999) from SQLUser.tbl_ticket_notiz where SUBSTRING(Bezeichnung, 1,9999999) LIKE '%pho%'

the Expression is still empty, when you take a look to the table, normaly the result should be the rows 8-11, this contains the word iPhone

here the Table 

and here the Table fields

maybe are this information helpful.

thx.

BR