CODEWITHSUNDEEP
sqlstringoracle-databasereverse
grey zeng
grey zeng

Reputation: 37

How to reverse the string 'ab,cd,ef' to 'ef->cd->ab'

when I select the table from Oracle, I want to handle one col'val : eg:

'ab,cd,ef' to 'ef->cd->ab';
'AB,BC' to 'BC->AB';
'ACNN,BBCCAC' to 'BBCCAC->ACNN';
'BBBDC，DCCX，FFF' to 'FFF->DCCX->BBBDC'

Upvotes: 2

Views: 355

Answers (2)

Lalit Kumar B
Lalit Kumar B

Reputation: 49082

You can do it with a mix of string split and string aggregation.

Using:

  • REGEXP_SUBSTR : To split the comma delimited string into rows
  • LISTAGG : To aggregate the values

You can have a look at this article to understand how string split works http://lalitkumarb.wordpress.com/2015/03/04/split-comma-delimited-strings-in-a-table-using-oracle-sql/

SQL> WITH DATA AS(
  2  SELECT 1 ID, 'ab,cd,ef' text FROM dual UNION ALL
  3  SELECT 2 ID, 'AB,BC' text FROM dual UNION ALL
  4  SELECT 3 ID, 'ACNN,BBCCAC' text FROM dual
  5  )
  6  SELECT ID,
  7    listagg(text, ',') WITHIN GROUP (
  8  ORDER BY rn DESC) reversed_indices
  9  FROM
 10    (SELECT t.id,
 11      rownum rn,
 12      trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text
 13    FROM data t,
 14      TABLE (CAST (MULTISET
 15      (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
 16      ) AS sys.odciNumberList ) ) lines
 17    ORDER BY ID
 18    )
 19  GROUP BY ID
 20  /

        ID REVERSED_INDICES
---------- ------------------------------
         1 ef,cd,ab
         2 BC,AB
         3 BBCCAC,ACNN

SQL>

Let's say your table looks like:

SQL> SELECT * FROM t;

        ID TEXT
---------- ------------------------------
         1 ab,cd,ef
         2 AB,BC
         3 ACNN,BBCCAC
         4 word1,word2,word3
         5 1,2,3

SQL>

Using the above query:

SQL> SELECT ID,
  2    listagg(text, '-->') WITHIN GROUP (
  3  ORDER BY rn DESC) reversed_indices
  4  FROM
  5    (SELECT t.id,
  6      rownum rn,
  7      trim(regexp_substr(t.text, '[^,]+', 1, lines.COLUMN_VALUE)) text
  8    FROM t,
  9      TABLE (CAST (MULTISET
 10      (SELECT LEVEL FROM dual CONNECT BY LEVEL <= regexp_count(t.text, ',')+1
 11      ) AS sys.odciNumberList ) ) lines
 12    ORDER BY ID
 13    )
 14  GROUP BY ID
 15  /

        ID REVERSED_INDICES
---------- ------------------------------
         1 ef-->cd-->ab
         2 BC-->AB
         3 BBCCAC-->ACNN
         4 word3-->word2-->word1
         5 3-->2-->1

SQL>

Upvotes: 0

APC
APC

Reputation: 146239

We have two tasks. The first is to tokenize the original strings. This is quite easy with regular expressions (although there are more performant approaches if you are dealing with large volumes). The second task is to re-assemble the tokens in reverse order; we can use the 11gR2 LISTAGG() function for this:

with tokens as (
    select distinct col1, regexp_substr(col1, '[^,]+', 1, level) as tkn, level as rn
    from t23
    connect by level <= regexp_count (col1, '[,]')  +1
   )
select col1
       , listagg(tkn, '->') 
            within group (order by rn desc) as rev_col1
from tokens
group by col1
/

Here is a SQL Fiddle.

Upvotes: 3

Related Questions