CODEWITHSUNDEEP
sqloracle-databaseoracle11g
user13058283
user13058283

Reputation: 1

Convert a series of Number values in Text in Oracle SQL Query

In the Oracle database, I have string values (VARCHAR2) like 1,4,7,8. The number represents as 1=car, 2= bus, 3=BB, 4=SB, 5=Ba, 6=PA, 7=HB, and 8 =G and want to convert the above-said example to "car,SB,HB,G" in my query results I tried to use "Decode" but it does not work. Please advise how to make it works. Would appreciate.

Thanks` Initially, I have used the following query:

    Select Clientid as C#, vehicletypeExclusions as vehicle  from 
    clients

The sample of outcomes are:

   C#   Vehicle
   20   1,19,20,23,24,7,5
   22   1,19,20,23,24,7,5

I also tried the following that gives me the null value of vehicles:

  Select Clientid as C#,  Decode (VEHICLETYPEEXCLUSIONS, '1', 'car', 
  '3','bus', '5','ba' ,'7','HB', '8','G'
  , '9','LED1102', '10','LED1104', '13','LED8-2',
  '14','Flip4-12', '17','StAT1003', '19','Taxi-Min', '20','Tax_Sed', 
   '21','Sup-veh'  , '22','T-DATS', '23','T-Mini',
   '24','T-WAM') as vehicle_Ex  from clients >

Upvotes: 0

Views: 572

Answers (3)

user5683823
user5683823

Reputation:

Assume you have a lookup table (associating the numeric codes with descriptions) and a table of input strings, which I called sample_inputs in my tests, as shown below:

create table lookup (code, descr) as
    select 1, 'car' from dual union all
    select 2, 'bus' from dual union all
    select 3, 'BB'  from dual union all
    select 4, 'SB'  from dual union all
    select 5, 'Ba'  from dual union all
    select 6, 'PA'  from dual union all
    select 7, 'HB'  from dual union all
    select 8, 'G'   from dual
;

create table sample_inputs (str) as
    select '1,4,7,8' from dual union all
    select null      from dual union all
    select '3'       from dual union all
    select '5,5,5'   from dual union all
    select '6,2,8'   from dual
;

One strategy for solving your problem is to split the input - slightly modified to make it a JSON array, so that we can use json_table to split it - then join to the lookup table and re-aggregate.

select s.str, l.descr_list
from   sample_inputs s cross join lateral
       ( select listagg(descr, ',') within group (order by ord) as descr_list
         from   json_table( '[' || str || ']', '$[*]'
                            columns code number path '$', ord for ordinality)
                join lookup l using (code)
       ) l
;

STR     DESCR_LIST                    
------- ------------------------------
1,4,7,8 car,SB,HB,G                   
                                      
3       BB                            
5,5,5   Ba,Ba,Ba                      
6,2,8   PA,bus,G

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Using the function f_subst from https://stackoverflow.com/a/68537479/429100 :

create or replace
function f_subst(str varchar2, template varchar2, subst sys.odcivarchar2list) return varchar2
as
    res varchar2(32767):=str;
begin
    for i in 1..subst.count loop
        res:=replace(res, replace(template,'%d',i), subst(i));
    end loop;
    return res;
end;
/

I've replaced ora_name_list_t (nested table) with sys.odcivarchar2list (varray) to make this example easier, but I would suggest to create your own collection for example create type varchar2_table as table of varchar2(4000);

Example:

select
   f_subst(
      '1,4,7,8'
     ,'%d'
     ,sys.odcivarchar2list('car','bus','BB','SB','Ba','PA','HB','G')
   ) s 
from dual; 

S
----------------------------------------
car,SB,HB,G

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142705

Here's one option. Read comments within code. Sample data in lines #1 - 13; query begins at line #14.

SQL> with
  2  expl (id, name) as
  3    (select 1, 'car' from dual union all
  4     select 2, 'bus' from dual union all
  5     select 3, 'BB'  from dual union all
  6     select 4, 'SB'  from dual union all
  7     select 5, 'Ba'  from dual union all
  8     select 6, 'PA'  from dual union all
  9     select 7, 'HB'  from dual union all
 10     select 8, 'G'   from dual
 11    ),
 12  temp (col) as
 13    (select '1,4,7,8' from dual),
 14  -- split COL to rows
 15  spl as
 16    (select regexp_substr(col, '[^,]+', 1, level) val,
 17            level lvl
 18     from temp
 19     connect by level <= regexp_count(col, ',') + 1
 20    )
 21  -- join SPL with EXPL; aggregate the result
 22  select listagg(e.name, ',') within group (order by s.lvl) result
 23  from expl e join spl s on s.val = e.id;

RESULT
--------------------------------------------------------------------------------
car,SB,HB,G

SQL>

Upvotes: 1

Related Questions