text to string using select

Hello! I want to extract some part of text field type and convert into string... I use a querry like these can i use o querry like this...select (CAST text_filed as varchar(8))..or something like that? maybee SUBSTRING perhaps?!.. ...i'm beginner with informix.... :) Thank you!

Hi, There is no simple way doing this, as far as i know. The problem is that TEXT cols are selected not by content but by reference. So these are not castable. (syntax would be select text_field::varchar (0) .....) The only way is to unload the content and reload it to a new table with varchar cols. This could be done using dbaccess. Of course, you have to care about the max length, which can be 2 GB in text cols And only 256 byte in varchar cols. The better way would be to restructure your db, because a text col is always very memory consuming, in case it holds only a representative of a varchar (8). Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of UKORU XAM Sent: Monday, June 05, 2006 12:00 PM To: ids@iiug.org Subject: text to string using select [6876] Hello! I want to extract some part of text field type and convert into string... I use a querry like these can i use o querry like this...select (CAST text_filed as varchar(8))..or something like that? maybee SUBSTRING perhaps?!.. .......i'm beginner with informix.... :) Thank you! **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

Thank you for your reply Marcus! I use .NET VB (Visual Studio ), and the problem is in line where I want to use rdoColumns; the content from text field is something like that: "c:\\\\path1\\\\path2\\\\pathfile.txt" The error appear when the return value of my informix querry is access with this line code rs.rdoColumns(0).Value ....I think that the result of the informix querry has a wrong type, so it didn't match with [rs.rdoColumns(0).Value] if I use select LENGTH(text_field) from my_informix_table...everything is ok but... select text_field from my_informix_table..don't work! I you have any idea what's going on, please help me! Thanks, XaM

Hi, As I said, Informix will return not a simple type in a text column, but a reference. This can only be read by using a stream (or something similar, I am not a user of VB .NET), either unloading/loading it to a string variable or an external file. If I understand you right, you want to query for a value of this text field as a condition. I am not sure if this will work. In plain SQL, there is no way to do something like "where field_text = 'sfdsdfs'". Informix offers a way to do this using datablades (for full-text search e.g). But this is rather complex and I have no experience with this. The easiest way to query for values is to make a second column or a parallel table, where the interesting part of your text column is stored as varchar or lvarchar (up to 32000 bytes). This can of course be used in a query and also allows indexing. Marcus -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of UKORU XAM Sent: Monday, June 05, 2006 2:41 PM To: ids@iiug.org Subject: Re: RE: text to string using select [6878] Thank you for your reply Marcus! I use .NET VB (Visual Studio ), and the problem is in line where I want to use rdoColumns; the content from text field is something like that: "c:\\\\path1\\\\path2\\\\pathfile.txt" The error appear when the return value of my informix querry is access with this line code rs.rdoColumns(0).Value ....I think that the result of the informix querry has a wrong type, so it didn't match with [rs.rdoColumns(0).Value] if I use select LENGTH(text_field) from my_informix_table...everything is ok but... select text_field from my_informix_table..don't work! I you have any idea what's going on, please help me! Thanks, XaM **************************************************************************** *** Forum Note: Use "Reply" to post a response in the discussion forum.

It's ok..thank you very much Marcus, I'll follow your advice and I'll post the code after everything is ok, perhaps it helps somebody in the future! Have a nice day! XaM