OraFAQ Forum: SQL & PL/SQL » Remove all entries except last one

Home » SQL & PL/SQL » SQL & PL/SQL » Remove all entries except last one (Oracle Database 11.2.0.3 Enterprise Edition)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Remove all entries except last one [message #612147]

Sun, 13 April 2014 11:56

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi,

I have to remove all entries of 'BARPAYG' in below piped strings except last one.

WITH temp AS (SELECT 'BARPAYG|BARPAYG|PROTECT' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC' col FROM DUAL
              UNION
              SELECT 'BARPAYG|BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG|GIGMRC' col FROM DUAL)
SELECT *
  FROM temp;

Report message to a moderator

Re: Remove all entries except last one [message #612149 is a reply to message #612147]

Sun, 13 April 2014 12:11

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

rows in a table are like balls in a basket.
which ball in the basket is the last ball?
rows in a table have NO inherent order!

Report message to a moderator

Re: Remove all entries except last one [message #612150 is a reply to message #612149]

Sun, 13 April 2014 12:23

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

... as it has been repeated and repeated to you many and many times.

Note that once you have defined an order (from the data themselves), you don't need regexp just substr and instr.

Report message to a moderator

Re: Remove all entries except last one [message #612151 is a reply to message #612150]

Sun, 13 April 2014 12:28

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

I nowhere written 'rows'. I mentioned 'piped strings', which is not equivalent to rows.

Suppose,

My pipelined entry is 'BARPAYG|BARPAYG|PROTECT', then I want result 'BARPAYG|PROTECT', by removing all but last occurence of 'BARPAYG' in the pipelined string.

Same for 'BARPAYG|ILDPPMRC|BARPAYG', result should be 'ILDPPMRC|BARPAYG'

Hope it will help to understand.

Manu

Report message to a moderator

Re: Remove all entries except last one [message #612153 is a reply to message #612151]

Sun, 13 April 2014 12:52

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SQL> WITH temp AS (SELECT 'BARPAYG|BARPAYG|PROTECT' col FROM DUAL
  2                UNION
  3                SELECT 'BARPAYG|ILDPPMRC' col FROM DUAL
  4                UNION
  5                SELECT 'BARPAYG|BARPAYG' col FROM DUAL
  6                UNION
  7                SELECT 'BARPAYG|ILDPPMRC|BARPAYG' col FROM DUAL
  8                UNION
  9                SELECT 'BARPAYG' col FROM DUAL
 10                UNION
 11                SELECT 'BARPAYG|ILDPPMRC|BARPAYG|GIGMRC' col FROM DUAL),
 12        data as (select '|'||col||'|' col from temp)
 13  select trim('|' from col) col,
 14         replace(
 15           trim('|' from
 16                case
 17                  when regexp_count(col, '|BARPAYG|') > 1
 18                    then regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')
 19                  else col
 20                end
 21               ),
 22           '||', '|') res
 23  from data
 24  /
COL                                 RES
----------------------------------- ------------------------------
BARPAYG                             BARPAYG
BARPAYG|BARPAYG                     BARPAYG
BARPAYG|BARPAYG|PROTECT             BARPAYG|PROTECT
BARPAYG|ILDPPMRC                    BARPAYG|ILDPPMRC
BARPAYG|ILDPPMRC|BARPAYG            BARPAYG|ILDPPMRC
BARPAYG|ILDPPMRC|BARPAYG|GIGMRC     BARPAYG|ILDPPMRC|GIGMRC

Report message to a moderator

Re: Remove all entries except last one [message #612154 is a reply to message #612153]

Sun, 13 April 2014 14:28

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

I did something like below

WITH temp AS (SELECT 'BARPAYG|BARPAYG|PROTECT' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC' col FROM DUAL
              UNION
              SELECT 'BARPAYG|BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG' col FROM DUAL
              UNION
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG|GIGMRC' col FROM DUAL)
SELECT col,
       REPLACE (SUBSTR (col, 1, CASE
                                   WHEN INSTR (col,
                                               'BARPAYG',
                                               -1,
                                               1) > 1 THEN INSTR (col,
                                                                  'BARPAYG',
                                                                  -1,
                                                                  1)
                                                           - 1
                                   ELSE 0
                                END), 'BARPAYG|', '')
       || SUBSTR (col, INSTR (col,
                              'BARPAYG',
                              -1,
                              1)) RES
  FROM temp
 WHERE col LIKE '%BARPAYG%BARPAYG%';

Manu

[Updated on: Sun, 13 April 2014 14:41]

Report message to a moderator

Re: Remove all entries except last one [message #612155 is a reply to message #612154]

Sun, 13 April 2014 14:52

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Hi Michel,

Can you please explain what is the logic in

regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')

Thanks,
Manu

Report message to a moderator

Re: Remove all entries except last one [message #612158 is a reply to message #612153]

Sun, 13 April 2014 16:05

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

Data magic:

WITH temp AS (SELECT 'BARPAYG|SOME_JUNK_BARPAYG|PROTECT' col FROM DUAL
             ),
      data as (select '|'||col||'|' col from temp)
select trim('|' from col) col,
       replace(
         trim('|' from
              case
                when regexp_count(col, '|BARPAYG|') > 1
                  then regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')
                else col
              end
             ),
         '||', '|') res
from data
/

COL                               RES
--------------------------------- -----------------------------------
BARPAYG|SOME_JUNK_BARPAYG|PROTECT BARPAYG|SOME_JUNK_|PROTECT

SQL>

SY.

Report message to a moderator

Re: Remove all entries except last one [message #612159 is a reply to message #612147]

Sun, 13 April 2014 16:50

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

WITH temp AS (
              SELECT 'BARPAYG|BARPAYG|PROTECT' col FROM DUAL UNION ALL
              SELECT 'BARPAYG|ILDPPMRC' col FROM DUAL UNION ALL
              SELECT 'BARPAYG|BARPAYG' col FROM DUAL UNION ALL
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG' col FROM DUAL UNION ALL
              SELECT 'BARPAYG' col FROM DUAL UNION ALL
              SELECT 'BARPAYG|ILDPPMRC|BARPAYG|GIGMRC' col FROM DUAL UNION ALL
              SELECT 'XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC' col FROM DUAL UNION ALL
              SELECT 'BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC' col FROM DUAL UNION ALL
              SELECT 'XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|PROTECT' col FROM DUAL
             ),
        t AS (
              SELECT  col,
                      INSTR(
                            col || '|',
                            '|BARPAYG|',
                            -1
                           ) pos
                FROM  temp
             )
SELECT  col,
        LTRIM(
              REGEXP_REPLACE(
                             SUBSTR(
                                    col,
                                    1,
                                    pos
                                    ),
                             '(^|\|)BARPAYG\|',
                             '|'
                            ) || SUBSTR(
                                        col,
                                        pos + 1
                                       ),
              '|'
             ) new_col
  FROM  t
/

COL                                           NEW_COL
--------------------------------------------- ---------------------------------------------
BARPAYG|BARPAYG|PROTECT                       BARPAYG|PROTECT
BARPAYG|ILDPPMRC                              BARPAYG|ILDPPMRC
BARPAYG|BARPAYG                               BARPAYG
BARPAYG|ILDPPMRC|BARPAYG                      ILDPPMRC|BARPAYG
BARPAYG                                       BARPAYG
BARPAYG|ILDPPMRC|BARPAYG|GIGMRC               ILDPPMRC|BARPAYG|GIGMRC
XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC
BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC     BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC
XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|PROTECT   XYZ|abc|def|BARPAYG|PROTECT

9 rows selected.

SQL>

SY.

[Updated on: Sun, 13 April 2014 17:37]

Report message to a moderator

Re: Remove all entries except last one [message #612160 is a reply to message #612159]

Sun, 13 April 2014 18:30

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

The condition you shown was in my mind, but I will not have the data like this, that's why I never included that in my example.

It will be really helpful if you can explain me:

then regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')

Manu

Report message to a moderator

Re: Remove all entries except last one [message #612162 is a reply to message #612160]

Sun, 13 April 2014 18:45

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I will let Michel explain what he meant by that regexp. Anyway, can you have:

WITH temp AS (
              SELECT 'XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|ghi|BARPAYG|PROTECT' col FROM DUAL
             ),
      data as (select '|'||col||'|' col from temp)
select trim('|' from col) col,
       replace(
         trim('|' from
              case
                when regexp_count(col, '|BARPAYG|') > 1
                  then regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')
                else col
              end
             ),
         '||', '|') res
from data
/

COL                                                     RES
------------------------------------------------------- --------------------------------------------------
XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|ghi|BARPAYG|PROTECT XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|ghi|PROTECT

SQL> 
WITH temp AS (
              SELECT 'XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|ghi|BARPAYG|PROTECT' col FROM DUAL
             ),
        t AS (
              SELECT  col,
                      INSTR(
                            col || '|',
                            '|BARPAYG|',
                            -1
                           ) pos
                FROM  temp
             )
SELECT  col,
        LTRIM(
              REGEXP_REPLACE(
                             SUBSTR(
                                    col,
                                    1,
                                    pos
                                    ),
                             '(^|\|)BARPAYG\|',
                             '|'
                            ) || SUBSTR(
                                        col,
                                        pos + 1
                                       ),
              '|'
             ) new_col
  FROM  t
/

COL                                                     NEW_COL
------------------------------------------------------- -------------------------------
XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|ghi|BARPAYG|PROTECT XYZ|abc|def|ghi|BARPAYG|PROTECT

SQL>

SY.

SY.

Report message to a moderator

Re: Remove all entries except last one [message #612163 is a reply to message #612162]

Sun, 13 April 2014 20:39

manubatham20
Messages: 566
Registered: September 2010
Location: Seattle, WA, USA

Senior Member

Yes, I can have data like this, and if you notice, my solution is giving correct result.

I am still waiting for Michel to explain that logic.

Manu

Report message to a moderator

Re: Remove all entries except last one [message #612169 is a reply to message #612158]

Mon, 14 April 2014 00:12

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Solomon Yakobson wrote on Sun, 13 April 2014 23:05

Data magic:

WITH temp AS (SELECT 'BARPAYG|SOME_JUNK_BARPAYG|PROTECT' col FROM DUAL
             ),
      data as (select '|'||col||'|' col from temp)
select trim('|' from col) col,
       replace(
         trim('|' from
              case
                when regexp_count(col, '|BARPAYG|') > 1
                  then regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')
                else col
              end
             ),
         '||', '|') res
from data
/

COL                               RES
--------------------------------- -----------------------------------
BARPAYG|SOME_JUNK_BARPAYG|PROTECT BARPAYG|SOME_JUNK_|PROTECT

SQL>

SY.

Yes I know and I was sure you'll come with an example like that. I even wrote a sentence telling so but I removed it to just let OP find the weakness of the expression. Laughing

Report message to a moderator

Re: Remove all entries except last one [message #612171 is a reply to message #612163]

Mon, 14 April 2014 00:28

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

regexp_replace(col, '(|BARPAYG|.*)BARPAYG|','\1')

Any expression between ( and ) are memorized and numbered.
Here (I omit the | in this explanation to simplify) there is only expression between ( and ): "|BARPAYG|.*" that is anything that starts with the first BARPAYG and it is numbered 1 (or \1).
This expression ends with the last "BARPAYG|" of the data not including it. Why the last, because regpexp specification says that it must find the longest possible string that matches the expression.
So now we replace the longest string starting with the first BARPAYG and ending with the last BARPAYG by the first expression (\1) numbered and this first expression is anything between the first BARPAYG and the last BARPAYG excluding the later.

It is not easy to explain it, I hope you now understand it but I let anyone that has a better way to explain it to do it.

The last problem is then to handle the fact it must be a strict BARPAYG and so it must be enclosed between |.

Report message to a moderator

Re: Remove all entries except last one [message #612181 is a reply to message #612171]

Mon, 14 April 2014 03:15

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Here's a "solution":

SQL> WITH temp AS (
  2                SELECT 'BARPAYG|BARPAYG|PROTECT' col FROM DUAL UNION ALL
  3                SELECT 'BARPAYG|ILDPPMRC' col FROM DUAL UNION ALL
  4                SELECT 'BARPAYG|BARPAYG' col FROM DUAL UNION ALL
  5                SELECT 'BARPAYG|ILDPPMRC|BARPAYG' col FROM DUAL UNION ALL
  6                SELECT 'BARPAYG' col FROM DUAL UNION ALL
  7                SELECT 'BARPAYG|ILDPPMRC|BARPAYG|GIGMRC' col FROM DUAL UNION ALL
  8                SELECT 'XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC' col FROM DUAL UNION ALL
  9                SELECT 'BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC' col FROM DUAL UNION ALL
 10                SELECT 'XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC|BARPAYG' col FROM DUAL UNION ALL
 11                SELECT 'BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|BARPAYG|GIGMRC' col FROM DUAL UNION ALL
 12                SELECT 'XYZ|BARPAYG|ILDPPMRC|BARPAYG|BARPAYG_SOME_JUNK|GIGMRC' col FROM DUAL UNION ALL
 13                SELECT 'BARPAYG|ILDPPMRC|BARPAYG|SOME_JUNK_BARPAYG|GIGMRC' col FROM DUAL UNION ALL
 14                SELECT 'XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|PROTECT' col FROM DUAL
 15               ),
 16  data as (select '#' || replace(col, '|', '|#') || '|' col from temp)
 17  select rtrim (substr (replace (col, '|#', '|'), 2), '|') col,
 18         rtrim (
 19           substr (
 20             replace (
 21               replace (
 22                 case
 23                   when regexp_count (col, '#BARPAYG\|') > 1
 24                     then regexp_replace (col, '(#BARPAYG\|.*)#BARPAYG|', '\1')
 25                   else col
 26                 end,
 27                 '||', '|'),
 28               '|#', '|') ,
 29             2),
 30           '|')
 31           res
 32  from data
 33  /
COL                                                   RES
----------------------------------------------------- ----------------------------------------------
BARPAYG|BARPAYG|PROTECT                               BARPAYG|PROTECT
BARPAYG|ILDPPMRC                                      BARPAYG|ILDPPMRC
BARPAYG|BARPAYG                                       BARPAYG
BARPAYG|ILDPPMRC|BARPAYG                              BARPAYG|ILDPPMRC
BARPAYG                                               BARPAYG
BARPAYG|ILDPPMRC|BARPAYG|GIGMRC                       BARPAYG|ILDPPMRC|GIGMRC
XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC         XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC
BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC             BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC
XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC|BARPAYG XYZ|BARPAYG|ILDPPMRC|BARPAYG_SOME_JUNK|GIGMRC
BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|BARPAYG|GIGMRC     BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC
XYZ|BARPAYG|ILDPPMRC|BARPAYG|BARPAYG_SOME_JUNK|GIGMRC XYZ|BARPAYG|ILDPPMRC|BARPAYG|_SOME_JUNK|GIGMRC
BARPAYG|ILDPPMRC|BARPAYG|SOME_JUNK_BARPAYG|GIGMRC     BARPAYG|ILDPPMRC|SOME_JUNK_BARPAYG|GIGMRC
XYZ|BARPAYG|abc|BARPAYG|def|BARPAYG|PROTECT           XYZ|BARPAYG|abc|BARPAYG|def|PROTECT

[Edit: add more data]

[Updated on: Mon, 14 April 2014 03:22]

Report message to a moderator

Re: Remove all entries except last one [message #612188 is a reply to message #612171]

Mon, 14 April 2014 05:40

Solomon Yakobson
Messages: 3273
Registered: January 2010
Location: Connecticut, USA

Senior Member

I also think you missed | is regexp special caharacter and is treated as OR if not escaped.

SY.

Report message to a moderator

Re: Remove all entries except last one [message #612193 is a reply to message #612188]

Mon, 14 April 2014 06:26

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Yes, I missed that (and forgot to mention it), now it is protected.
It is a special character when used between () or between [] or in regexp_count.

Report message to a moderator

Previous Topic:	can any one help me in forming the below requested query
Next Topic:	Remove the department in dept table where dept does not have any employees?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 00:25:20 CDT 2024