在Oracle SQL查询中转换一系列Text中的Number值

Question

在Oracle数据库中，我有字符串值(VARCHAR2)，如1,4,7,8。数字表示为1=car, 2= bus, 3=BB, 4=SB, 5=Ba, 6=PA, 7=HB, 8 =G并希望将上述示例转换为"car,SB,HB, g";在我的查询结果我试着用"解码"但这行不通。请告知如何使其工作。会升值。

谢谢"最初，我使用了以下查询:

Select Clientid as C#, vehicletypeExclusions as vehicle  from 
clients 

结果样本为:

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

我还尝试了以下给我车辆的空值:

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 >

Answer 1

这里有一个选择。阅读代码中的注释。第1 - 13行中的样本数据;查询从第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>

Answer 2

使用来自https://stackoverflow.com/a/68537479/429100:

的f_subst函数

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;
/

我已经用sys.odcivarchar2list(数组)取代了ora_name_list_t(嵌套表)，使这个例子更容易，但我建议创建您自己的集合，例如create type varchar2_table as table of varchar2(4000);

的例子:

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

Answer 3

假设您有一个查找表(将数字代码与描述关联起来)和一个输入字符串表，我在测试中将其称为sample_inputs，如下所示:

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
;

解决这个问题的一个策略是分割输入——稍微修改一下使它成为一个JSON数组，这样我们就可以使用json_table来分割它——然后连接到查找表并重新聚合。

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