Decode values of a column in plsql

Question

I have table in which there is a column which contains values as 1:2 or 2:3 or 2:3:4 etc. I need to decode these values on the basis of their values mentioned in the different table. like 1 is x and 2 is y.

there are 5 values in the column. earlier only one value were there where name against that values was being fetched from other table by join condition. But now any combination of 5 values can be there separated by ":"-colon. Please suggest how to get names of these values for a column. Let me know if any other detail is required

Please suggest a way to implement this.

Answer 1

Hi here how you need to go; first start with inner query it will give all values in you column like 1, 2,3, 4 etc. then need to create mapping table eg. 1 to 'x' , 2 to 'y' physically or logically as I have done and select from mapping table as per result from inner query which is your spitted column values.

with map_data as (
      select 1 as d_value , 'X' as m_value from dual
      union all
      select 2 as d_value , 'Y' as m_value from dual
      union all
      select 3 as d_value , 'Z' as m_value from dual)
    select * from map_data
    where d_value in(
    WITH data AS (
      SELECT '1:2:3' AS "value" FROM DUAL
      UNION ALL
      SELECT '2:4' AS "value" FROM DUAL
    )
    SELECT  REGEXP_SUBSTR( data."value", '[^:]+', 1, levels.COLUMN_VALUE )
    FROM    data,
            TABLE(
              CAST(
                MULTISET(
                  SELECT LEVEL
                  FROM   DUAL
                  CONNECT BY  LEVEL <= LENGTH( regexp_replace( "value", '[^:]+'))  + 1
                ) AS sys.OdciNumberList
              )
            ) levels)