0
524
Question darko susnjara · Feb 20, 2020

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

#Caché #SQL

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

Discussion (2)0
Log in or sign up to continue

Comments

Benjamin De Boe · Feb 20, 2020

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.

0
darko susnjara  Feb 20, 2020 to Benjamin De Boe

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

0