I have some field with comma-separated values:
1,2,AA,4,Z
1,ZZ,44,A,1
1,44,33,AA,Z
4,2,Z,A,F
1.2,4,E,1,1
F,1,3.4,3,A
Is it possible to select rows with rule: number,number,chars, anything_values?
Result can be:
1,2,AA,4,Z
4,2,Z,A,F
1.2,4,E,1,1
I'm trying to use REGEXP_LIKE:
with s as (
select '1,2,AA,4,Z' val from dual
union all
select '1,ZZ,44,A,1' val from dual
union all
select '1,44,33,AA,Z' val from dual
union all
select '4,2,Z,A,F' val from dual
union all
select '1.2,4,E,1,1' val from dual
union all
select 'F,1,3.4,3,A' val from dual
)
select val from s where REGEXP_LIKE(val, '(^|,)[[:digit:]](,|$)');
Thank you for the CTE.
Would something like this do?
INTER splits every VAL into rowsLISTAGG aggregates them back, sorted by numbers and charactersSQL> with s as (
2 select '1,2,AA,4,Z' val from dual
3 union all
4 select '1,ZZ,44,A,1' val from dual
5 union all
6 select '1,44,33,AA,Z' val from dual
7 union all
8 select '4,2,Z,A,F' val from dual
9 union all
10 select '1.2,4,E,1,1' val from dual
11 union all
12 select 'F,1,3.4,3,A' val from dual
13 ),
14 inter as
15 (select s.val,
16 regexp_substr(s.val, '[^,]+', 1, t.column_value) c_one
17 from s, table(cast(multiset(select level from dual
18 connect by level <= regexp_count(s.val, ',') + 1
19 ) as sys.odcinumberlist )) t
20 )
21 select i.val, listagg(i.c_one, ',') within group
22 (order by case when regexp_like(i.c_one, '^\d+|\.|\d+$') then 1
23 when regexp_like(i.c_one, '^[:alpha:]+') then 2
24 else 3
25 end) result
26 from inter i
27 group by i.val;
VAL RESULT
------------ --------------------
1,2,AA,4,Z 1,2,4,AA,Z
1,44,33,AA,Z 1,33,44,AA,Z
1,ZZ,44,A,1 1,1,44,A,ZZ
1.2,4,E,1,1 1,1,1.2,4,E
4,2,Z,A,F 2,4,A,F,Z
F,1,3.4,3,A 1,3,3.4,A,F
6 rows selected.
SQL>