Search long varchar SQL

Question

I have a database in Ingres 9.2.0 that contains a long varchar column. Is there any way that I can search this column for a specific string and only return the rows that contain have that string in the column.

I tried using like as follows

select * from table
where col like '%abc%'

but I get the following error

Function 'like' is not defined for arguments of type 'long varchar' and 'varchar'. Explicitly convert its arguments to the desired type(s).

I was able to resolve this with the following query

select * from table WHERE position('abc' in col) > 0

From `long varchar` sounds like maybe DB2? Please clarify this as the answer is dependant on which RDBMS — Seph, Dec 14 '11 at 11:15
It seems to be for Ingres: http://lists.ingres.com/pipermail/svn-commits/2009-January/001085.html — ypercubeᵀᴹ, Dec 14 '11 at 14:59

grantc · Accepted Answer · 2011-12-15T12:54:28.143

3

Support for LIKE against LONG VARCHAR was added to Ingres 9.3.0 (project page - If you have an earlier release this won't work.See the feature list for all 9.3 changes.

edited Dec 15 '11 at 12:54

answered Dec 15 '11 at 11:12

grantc

1,693
12
13

I have version 9.2.0, does that mean that the above query is incorrect? Thanks – Stina Dec 15 '11 at 11:55
If you get the error from the OP, then you *do not* have a version of Ingres which supports LIKE on LONG VARCHARs. – DejanLekic Dec 15 '11 at 12:25
it was added in 9.3 not 9.2, I've updated the above text to reflect that. – grantc Dec 15 '11 at 12:54
@grantc thanks for your help, I have edited the question with the query that I used to resolve it. – Stina Dec 15 '11 at 14:43

Search long varchar SQL

1 Answers1