Any way to cast SET into VARCHAR?
Hugo Zambrano asked how to cast a SET value to VARCHAR to strip the SET word and brackets. Art Kagel showed that casting to LVARCHAR works, and Hugo confirmed. Brad Patterson also cast sets explicitly to LVARCHAR and used a PHP function to parse the SET{...} string into an array. Solved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
I want to convert an SQL output, a SET value, into a VARCHAR in order to use the string functions to remove the SET word, the brackets, and the quotes, to only leave commas separating each item. I tried a cast but Informix complain it cannot be done, but perhaps you guys know a trick. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------ #Informix
Hugo:
Try this:
> info columns for multi_test;
Column name Type Nulls
one serial no
two list yes
three set yes
four multiset yes
> select three from multi_test;three SET{'a ','b ','c '}
1 row(s) retrieved.
> select '"'||substr(three::lvarchar,5,length(three::lvarchar)-5)||'"' from multi_test;
(expression) "'a ','b ','c '"
Art S. Kagel, President and Principal Consultant
ASK Database Management
www.askdbmgt.com
Blog:
http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Oh man, it worked. It did not occurred to me to use LVARCHAR instead of VARCHAR or CHAR. Thanks a lot. ------------------------------ Zambrano, Hugo Informix DBA Ottawa Police Ottawa, ON (613)236-1222, 5575 ------------------------------
We cast SET types explicitly into LVARCHAR to manipulate them: SELECT SET_FIELD::LVARCHAR AS SET_FIELD FROM TABLE; The output includes the syntax structures (the SET key word and the brackets and commas): set_field SET{500 ,499 ,360 ,100} We then use a PHP function to convert this output to an array: function setToArray($pSet) { # if the string is empty then return an array with no elements if (is_string($pSet)) { # receive the LVARCHAR representation of a set, in the following format: SET{1, 15, 23} # and turn it into an array for return $A = explode(",", substr($pSet, 4, (strlen($pSet) - 5))); return $A; } else { return array(); } } # end function setToArray Regards, Brad Patterson -- This e-mail may contain confidential or privileged information. If you believe you have received this e-mail in error, please notify the sender by reply e-mail and then delete this e-mail immediately.