Home » SQL & PL/SQL » SQL & PL/SQL » Mark Only Selected Portion of a Column Data (12.1)
Mark Only Selected Portion of a Column Data [message #684138] Thu, 08 April 2021 13:17 Go to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
Can you please advice how can i mask/update all the rows of tables with only selected portion of a Column
data type is varchar2(250)
447 K rows in Table

sample data in table below.
TRAN_FEST_DATA
Type=CHECKS , TransCount=1 , HashTotal=452.46 , FirstAcct=[b][color=red]0000000000039550[/color][/b]7183 , FirstChk=000000000000010804 , LastAcct=[color=red][b]0000000000039550[/b][/color]8317 , LastChk=000000000000010804
Type=ADP , TransCount=5 , HashTotal=6454.83 , FirstAcct=[b][color=red]0000000000000427[/color][/b]1483 , FirstChk=000000000023222156 , LastAcct=[b][color=red]0000000000000427[/color][/b]8341 , LastChk=000000000023234723
Type=CASH , TransCount=8 , HashTotal=36199.89 , FirstAcct=[b][color=red]0000000000021537[/color][/b]1761 , FirstChk=000000000051478685 , LastAcct=[b][color=red]0000000000021537[/color][/b]6176 , LastChk=000000000052199091
basically from the each rows of TRAN_FEST_DATA Column, from the contents that include FirstAcct= and LastAcct=
should be masked.

Thanks and Appreciate your advice.

Report message to a moderator

Re: Mark Only Selected Portion of a Column Data [message #684140 is a reply to message #684138] Thu, 08 April 2021 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

With any SQL or PL/SQL question, please, post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Report message to a moderator

Re: Mark Only Selected Portion of a Column Data [message #684144 is a reply to message #684140] Thu, 08 April 2021 16:40 Go to previous messageGo to next message
azeem87
Messages: 116
Registered: September 2005
Location: dallas
Senior Member
 
CREATE TABLE TRANS_FEST_DETAIL
(
  TRANS_ID        VARCHAR2(25 BYTE)   NOT NULL,
  FILE_TYPE      VARCHAR2(10 BYTE),
  TRAN_FEST_DATA  VARCHAR2(250 BYTE),
  CRE_TS         TIMESTAMP(6) DEFAULT SYSDATE  NOT NULL);

ALTER TABLE TRANS_FEST_DETAIL ADD ( PRIMARY KEY   (TRANS_ID) ENABLE VALIDATE);

SET DEFINE OFF;
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005264026', 'PRA', 'Type=CHECKS , TransCount=1 , HashTotal=364.73 , FirstAcct=00000000000199488772 , FirstChk=000000000000005296 , LastAcct=00000000000199488772 , LastChk=000000000000005296', TO_TIMESTAMP('10/21/2019 7:15:25.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005264028', 'PRA', 'Type=CHECKS , TransCount=96 , HashTotal=13985.03 , FirstAcct=00000000000839950300 , FirstChk=000000000076007444 , LastAcct=00000000000839950300 , LastChk=000000000076007539', TO_TIMESTAMP('10/21/2019 7:15:38.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005264022', 'PRA', 'Type=CHECKS , TransCount=249 , HashTotal=272498.34 , FirstAcct=00000000000124775480 , FirstChk=000000000300021809 , LastAcct=00000000000124775480 , LastChk=000000000300029747', TO_TIMESTAMP('10/21/2019 7:14:55.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005264024', 'PRA', 'Type=CHECKS , TransCount=3 , HashTotal=232.00 , FirstAcct=00000000000908577362 , FirstChk=000000000015572903 , LastAcct=00000000000978999863 , LastChk=000000000050538640', TO_TIMESTAMP('10/21/2019 7:15:10.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005310690', 'PRA', 'Type=CHECKS , TransCount=249 , HashTotal=272498.34 , FirstAcct=00000000000124775480 , FirstChk=000000000300021809 , LastAcct=00000000000124775480 , LastChk=000000000300029747', TO_TIMESTAMP('10/29/2019 5:30:04.000000 PM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005312082', 'PRA', 'Type=CHECKS , TransCount=249 , HashTotal=272498.34 , FirstAcct=00000000000124775480 , FirstChk=000000000300021809 , LastAcct=00000000000124775480 , LastChk=000000000300029747', TO_TIMESTAMP('10/30/2019 8:52:55.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005309450', 'PRA', 'Type=CHECKS , TransCount=249 , HashTotal=272498.34 , FirstAcct=00000000000124775480 , FirstChk=000000000300021809 , LastAcct=00000000000124775480 , LastChk=000000000300029747', TO_TIMESTAMP('10/29/2019 10:05:21.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
Insert into TRANS_FEST_DETAIL
   (TRANS_ID, FILE_TYPE, TRAN_FEST_DATA, CRE_TS)
 Values
   ('300000005309454', 'PRA', 'Type=CHECKS , TransCount=1 , HashTotal=364.73 , FirstAcct=00000000000199488772 , FirstChk=000000000000005296 , LastAcct=00000000000199488772 , LastChk=000000000000005296', TO_TIMESTAMP('10/29/2019 10:05:47.000000 AM','fmMMfm/fmDDfm/YYYY fmHH12fm:MI:SS.FF AM'));
COMMIT;

it should look like this
Type=CHECKS , TransCount=1 , HashTotal=737.46 , FirstAcct=XXXXXXXXXXXX8317 , FirstChk=000000000000010804 , LastAcct=XXXXXXXXXXXX8317 , LastChk=000000000000010804
mask all digits except last 4

Report message to a moderator

Re: Mark Only Selected Portion of a Column Data [message #684145 is a reply to message #684140] Thu, 08 April 2021 16:46 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3213
Registered: January 2010
Location: Connecticut, USA
Senior Member
 
WITH SAMPLE AS (
                SELECT 'Type=CHECKS , TransCount=1 , HashTotal=452.46 , FirstAcct=00000000000395507183 , FirstChk=000000000000010804 , LastAcct=00000000000395508317 , LastChk=000000000000010804' STR FROM DUAL
               )
SELECT  REGEXP_REPLACE(STR,'(^| )(FirstAcct=|LastAcct=)\d+(\d{4})','\1\2\3') MASKED_STR
  FROM  SAMPLE
/

MASKED_STR
-----------------------------------------------------------------------------------------------------------------------------------------
Type=CHECKS , TransCount=1 , HashTotal=452.46 , FirstAcct=7183 , FirstChk=000000000000010804 , LastAcct=8317 , LastChk=000000000000010804

SQL>
SY.

Report message to a moderator

Re: Mark Only Selected Portion of a Column Data [message #684147 is a reply to message #684144] Fri, 09 April 2021 00:20 Go to previous message
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 
SQL> with
  2    data as (
  3      select 'Type=CHECKS , TransCount=1 , HashTotal=452.46 , FirstAcct=00000000000395507183 , FirstChk=000000000000010804 , LastAcct=00000000000395508317 , LastChk=000000000000010804'
  4               val
  5      from dual
  6    )
  7  select regexp_replace(val,'(^| )(FirstAcct=|LastAcct=)\d{16}(\d+)','\1\2XXXXXXXXXXXXXXXX\3') res
  8  from data
  9  /
RES
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Type=CHECKS , TransCount=1 , HashTotal=452.46 , FirstAcct=XXXXXXXXXXXXXXXX7183 , FirstChk=000000000000010804 , LastAcct=XXXXXXXXXXXXXXXX8317 , LastChk=000000000000010804

[Updated on: Fri, 09 April 2021 00:21]

Report message to a moderator

Previous Topic: Display zero if negative value.
Next Topic: Query to Display date
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Mar 28 23:06:01 CDT 2023
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.