Convert TEXT to NVARCHAR?

[gkrenton]

So it seems that informix won't let me substr or substring a TEXT field....Anyone know how I can convert a TEXT field into a Nvarchar or something else that I can then put into a distinct sql query?

- gina

 

[mberni]

Hi,

you cannot address text-fields via string-functions, cause these are BLOB-Fields, which must be handled diffently.

what you can do:

- load/unload text-fields via unload/load-statements

i.e. you could do:

create table m1 (field1 text, field2 varchar(255));

create a text-file m1test.unl with following content:

This is a text|<blank>

load this textfile into your table:

load from m1test.unl insert into m1;

select contents from table using subscripts

select field1[1,4] from m1;

will result in

field1
This

you cannot

update m1 set field2=field1[1,255];

so you could do something like that:

unload to m1trans.unl select field2, field1 from m1;
delete from m1 where 1=1;
load from m1trans.unl insert into m1;

hope this helps!

 

[gkrenton]

Thanks - complicated but helpful but at least it's a workable solution.
I appreciate the info.

- Gina