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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
Another option should be:

SELECT DISTINCT substr(text,1,length(text)) ...
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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.
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...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.