Informix SQL : select distinct and text field

In Informix, is there a way to convert or cast a text field so that i am able to SELECT DISTINCT ?
LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?
pcelbaConnect With a Mentor Commented:
You have to do it yourself in Informix and write some User defined routine that will calculate the checksum (it is mentioned e.g. here:

The checksum routine result then can be used in SELECT DISTINCT.

Text data type is not so friendly in Informix as text or even varchar(max) data type on SQL Server. Maybe it could be a reason to swap the platform...
You should convert the text to lvarchar:


It is not obvious to select distinct records this way. Better solution is to calculate some checksum from TEXT column and then select distinct based on this checksum.
zephyr_hex (Megan)DeveloperAuthor Commented:
before i posted, i tried cast(mytextfield as varchar(255))

i tried your suggestion, and i'm still getting the same message:  No cast from text to lvarchar.
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

Another option should be:

SELECT DISTINCT substr(text,1,length(text)) ...
If above solution fails, then you cannot SELECT DISTINCT from text column and the only solution is to write/find out some checksum function.
zephyr_hex (Megan)DeveloperAuthor Commented:
can you provide an example of how the checksum would work?

also... does Informix just not allow that type of conversion?  i can do it in SQL 2005.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.