Select dynamic pattern matching string from a column in oracle

Question

I was doing a query in oracle in which i have to update a col2 using col1 specified in col1 so data in col1 is dynamic i.e. there can be any no. Of / So i want ... Col2 be updated as trimmed value from the last slash of col1.

Input

Col1        Col2
a          data
a/bb        data
a/b/c      data
a/bbc/c/d/    data

Output

   Col1        Col2
    a          data
    a/b        data/a
    a/b/c      data/a/b
    a/bbc/c/d/    data/a/bbc/c

I tried it to do it using regexp_substr but it's dynamic. My query is working for only one or two slashes something like this [^/]{1}

So, help me do it dynamically

MT0 · Accepted Answer · 2017-10-10 11:16:17Z

1

You can use INSTR to find the last occurrence of a character and SUBSTR to remove it:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( Col1, Col2 ) AS
SELECT 'a',          'data' FROM DUAL UNION ALL
SELECT 'a/b',        'data' FROM DUAL UNION ALL
SELECT 'a/b/c',      'data' FROM DUAL UNION ALL
SELECT 'a/b/c/d',    'data' FROM DUAL UNION ALL
SELECT 'a/b/c/d/ef', 'data' FROM DUAL;

Query 1:

SELECT col1,
       RTRIM(
         col2 || '/' || SUBSTR( col1, 1, INSTR( col1, '/', -1 ) - 1 ),
         '/'
       ) AS col2
FROM   table_name

Results:

|       COL1 |         COL2 |
|------------|--------------|
|          a |         data |
|        a/b |       data/a |
|      a/b/c |     data/a/b |
|    a/b/c/d |   data/a/b/c |
| a/b/c/d/ef | data/a/b/c/d |

answered Oct 10, 2017 at 11:16

MT0

173k12 gold badges70 silver badges136 bronze badges

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

3 Comments

steve Over a year ago

Thanx MTO .. it worked but there is a little update just look at my updated input and output if there will be a case like row4 then how last slash would be trimmed

MT0 Over a year ago

@steve Replace both col1 with RTRIM( col1, '/' ) - SQLFIDDLE

steve Over a year ago

Thankx mam@mt0 i put rtrim inside the instr and it worked ... And i also added a replace function outside substr to replace '/' into '\'

Gordon Linoff · Accepted Answer · 2017-10-10 11:42:08Z

1

For your given data, this does what you want:

update t
    set col2 = col2 || '/' || substr(col1, 1, -2);

I suspect that may not be the general solution you are looking for, though.

EDIT:

I keep thinking there should be a better way, but this should work:

update t
    set col2 = col2 || '/' || substr(col1, 1, length(col1) - coalesce(length(regexp_substr(col1, '/[^/]*$', 1, 1)), 0));

Yes, a simpler version is:

update t
    set col2 = col2 || '/' || regexp_replace(col1, '/[^/]*$', '')

edited Oct 10, 2017 at 11:42

answered Oct 10, 2017 at 11:08

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

steve Over a year ago

Hey Gordon can you plz elaborate me this regexp_replace(col1, '/[^/]*$', '') part .... i did not get that completely

Gordon Linoff Over a year ago

@steve . . . It searches for the last forward slash and all the characters after it.

Collectives™ on Stack Overflow

Select dynamic pattern matching string from a column in oracle

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related