Oracle
使用“Listagg”函式和“正則表達式”來反轉 Oracle 中特定列的值
我有一個具有以下結構的表:
create table TBL_TEST ( col_id NUMBER, col_name VARCHAR2(500) )
一些範例數據:
col_id | col_name ----------------- 1 | aetnap 2 | elppa 3 | ananab
我想要做的是這樣
reverse
的列的值col_name
:col_id | col_name ----------------- 1 | pantea 2 | apple 3 | banana
我曾經
listagg and regexp_replace
寫過查詢:select col_id, listagg(val) within group(order by val_row desc) original_value from (select col_id, val, row_number() over(partition by col_id order by col_id) val_row from (select col_id, trim(column_value) val from tbl_test, xmltable(trim(trailing ',' from regexp_replace(col_name, '(.)', '"\1",'))))) group by col_id;
上面的查詢工作正常並給了我想要的結果,我想知道是否有更好的方法來編寫查詢,因為上面的查詢中使用了兩個子查詢,我想知道是否有比使用子查詢更好的方法.
提前致謝
select col_id, reverse(col_name) as col_name from tbl_test; COL_ID COL_NAME ---------- ---------- 1 pantea 2 apple 3 banana