Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Any way to cast SET into VARCHAR?

Jump to Best Answer
  • 1.  Any way to cast SET into VARCHAR?

    Posted 26 days ago
    Edited by Hugo Zambrano 26 days ago
    ​​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
    ------------------------------


  • 2.  RE: Any way to cast SET into VARCHAR?
    Best Answer

    Posted 26 days ago
    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


    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.








  • 3.  RE: Any way to cast SET into VARCHAR?

    Posted 26 days ago
    Edited by Hugo Zambrano 26 days ago
    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
    ------------------------------



  • 4.  RE: Any way to cast SET into VARCHAR?

    Posted 25 days ago
    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.