Search code examples
phpstringoraclehexnvarchar

NVARCHAR2 is being represented as hex

I have an NVARCHAR2 field in Oracle and I'm doing a LISTAGG and appending some text to it.

LISTAGG(
  CASE
    WHEN a.is_dead = 'Y' AND a.death_cause IS NOT NULL AND a.death_date IS NOT NULL THEN a.composite_pednum || ' - ' || a.death_cause || ' (' || a.death_date || ')'
    WHEN a.is_dead = 'Y' AND a.death_cause IS NOT NULL THEN a.composite_pednum || ' - ' || a.death_cause
    WHEN a.is_dead = 'Y' THEN a.composite_pednum || ' - Dead'
    WHEN a.is_dead = 'N' THEN a.composite_pednum || ' - Alive'
    ELSE a.composite_pednum || ' - Unknown Status'
  END,
  ';'
) WITHIN GROUP (ORDER BY a.pedigree_number ASC) AS cage_animals

In PHP, I explode this text to turn it into an array for processing later.

$cage_animals = array();
if(!empty($data['CAGE_ANIMALS'][$i])) {
  $cage_animals = explode(';', $data['CAGE_ANIMALS'][$i]);
}

Which gives me something like

["cage_animals"]=>
  array(2) {
    [0]=>
    string(38) "R15-57713-B - Alive"
    [1]=>
    string(40) "R15-57714-RR - Alive"
  }

Notice how the length of the string looks more than it actually is? I think this might be because of the following screenshot (not the full screenshot):

enter image description here

So my question is how do I prevent this? I'm trying to find the strpos of Alive and I always get false because the text isn't found.

Solution

  • I ended up using either CAST or TO_CHAR for each of my THEN clause.

    LISTAGG(
  CASE
    WHEN a.is_dead = 'Y' AND a.death_cause IS NOT NULL AND a.death_date IS NOT NULL THEN TO_CHAR(a.composite_pednum || ' - ' || a.death_cause || ' (' || TO_CHAR(a.death_date, 'Mon DD, YYYY') || ')')
    WHEN a.is_dead = 'Y' AND a.death_cause IS NOT NULL THEN TO_CHAR(a.composite_pednum || ' - ' || a.death_cause)
    WHEN a.is_dead = 'Y' THEN TO_CHAR(a.composite_pednum || ' - Dead')
    WHEN a.is_dead = 'N' THEN TO_CHAR(a.composite_pednum || ' - Alive')
    ELSE TO_CHAR(a.composite_pednum || ' - Unknown Status')
  END,
  ';'
) WITHIN GROUP (ORDER BY a.pedigree_number ASC) AS cage_animals