Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2010

    Unanswered: Array update & concatenation issue


    My issue is this :

    Table : test
    Field : sign_desc character varying(80)[]
    Values : {"SIGN #1","Data1","SIGN #2","Data2","SIGN #3"}

    I need to concatenate elemenst 1&2,3&4,5&6 and so on from sign_desc and put into into an array field in the same table.

    Required as : {"SIGN #1 Data1","SIGN #2 Data2","SIGN #3"}

    The solution I saw was to alter the existing test table, add a new array field and input the concatenated array. However, I am stuck on a very manual approach :

    ,case when array_upper(sign_desc,1) = 1 then sign_desc[1]
    when array_upper(sign_desc,1) >= 2 then sign_desc[1]||' '||coalesce(sign_desc[2],'')
    end as ar_v1

    ,case when array_upper(sign_desc,1) >= 3 then sign_desc[3]||' '||coalesce(sign_desc[4],'')
    end as ar_v2
    ,case when array_upper(sign_desc,1) >=5 then sign_desc[5]||' '||coalesce(sign_desc[6],'')
    end as ar_v3
    array_upper(sign_desc,1) is not null and
    array_upper(sign_desc,1) > 0

    After altering the test table and adding a new field, how would my update SQL look like? I need an approach wherein based on the array length(array_upper),i can define the length of the array and input as many values only.Any sugestions/approaches welcome.

    Last edited by naheedsk; 06-15-10 at 08:07.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts