Returning rows with comma-separated values by pattern (Oracle SQL)

Question

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:]](,|$)');

Could you explain further what your expected output is?

Joakim Danielson
– Joakim Danielson

2019-01-22 09:08:23 +00:00
Commented Jan 22, 2019 at 9:08 — Joakim Danielson
– Joakim Danielson, Commented Jan 22, 2019 at 9:08

Joakim Danielson · Accepted Answer · 2019-01-22 08:15:27Z

2

This regular expresion should work '^([0-9.]*,)([0-9.]*,)([A-Z]+)' if you want to return only the rows that matches the pattern number, number, word, anything

select val from s where REGEXP_LIKE(val, '^([0-9.]*,)([0-9.]*,)([A-Z]+)');

answered Jan 22, 2019 at 8:15

Joakim Danielson

53k5 gold badges36 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Littlefoot · Accepted Answer · 2019-01-22 08:09:48Z

1

Thank you for the CTE.

Would something like this do?

INTER splits every VAL into rows
LISTAGG aggregates them back, sorted by numbers and characters

SQL> 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>

answered Jan 22, 2019 at 8:09

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

2 Comments

Joakim Danielson Over a year ago

I don't understand this answer, it rearranges the values rather than selecting only the ones that matches the pattern. And what is the logic for rearranging them, seems to be all numbers first then characters which doesn't fit the pattern.

Littlefoot Over a year ago

Hm, @Joakim, I understand your question. I thought that source data have to be rearranged to match the target pattern. I might have been wrong, I agree.

Collectives™ on Stack Overflow

Returning rows with comma-separated values by pattern (Oracle SQL)

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related