0

I have a table table1 with 1 column - edi_value which is of type CLOB.

These are the entries:

seq  edi_message
1    ISA*00*          *00*          *08*9254110060     *ZZ*123456789      *041216*0805*U*00501*000095071*0*P*>~
    GS*AG*5137624388*123456789*20041216*0805*95071*X*005010~
    ST*824*021390001*005010X186A1~

2    ISA*00*          *00*          *08*56789876678     *ZZ*123456789      *041216*0805*U*00501*000095071*0*P*>~
    GS*AG*5137624388*123456789*20041216*0805*95071*X*005010~
    ST*824*021390001*005010X186A1~

Please note - there can be varying number of lines, from 3 to 500.

What I'm looking for is the following conditions:

  • Ignore text before first * in each line, for every line, before the first *, it should not change. For ex. GS, ST should not change. ONLY after the first * should randomize
  • Replace numbers [0-9] with random numbers, for ex. if 0 is replaced with 1, then it should be 1 througout.
  • Replace text [A-Za-z] with random text, for ex. if A is replaced with W, then it should be replaced with W throughout
  • Leave special characters as is

One character/number should ONLY map to one random character/number

Output can be:

seq  edi_message
1    ISA*11*          *11*          *13*4030111101     *QQ*102030234      *101010*1313*U*11311*111143121*1*V*>~
    GS*WE*3122000233*102030234*01101010*1313*43121*X*113111~
    ST*300*101241111*113111X130A1~

2    ISA*11*          *11*          *13*30234320023     *QQ*102030234      *101010*1313*U*11311*111143121*1*V*>~
    GS*WE*3122000233*102030234*01101010*1313*43121*X*113111~
    ST*300*101241111*113111X130W1~

How can this be achieved in Oracle SQL?

Improve this question

2 Answers 2

Reset to default
3

You can use translate with a helper function for generating random strings (though @LukStorms has a much neater SQL solution for that using LISTAGG), along with a method to tokenise and then re-concatenate the values into lines (I use a pure SQL method here for demonstration):

create or replace function f(p_low integer, p_high integer) 
    return varchar as
  r varchar(2000) := '';
  x integer;
begin
  for i in p_low..p_high loop
    x := dbms_random.value(0,length(r)+1);
    r := substr(r,1,x)||chr(i)||substr(r,x+1);
  end loop;
  return r;
end;
/

select * from table1;

| EDI_VALUE                                                                                                                                                                                                        |
| :--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ISA*00*          *00*          *08*9254110060     *ZZ*123456789      *041216*0805*U*00501*000095071*0*P*>~<br>    GS*AG*5137624388*123456789*20041216*0805*95071*X*005010~<br>    ST*824*021390001*005010X186A1~ |
| ISA*00*          *00*          *08*56789876678     *ZZ*123456789      *041216*0805*U*00501*000095071*0*P*>~<br>    GS*AG*5137624388*123456789*20041216*0805*95071*X*005010~<br>    ST*824*021390001*005010X186A  |

with t as (select f(48,57)||f(65,90) translate_chars from dual)
select (select new_value
        from (select substr(sys_connect_by_path(r_line,'
'),2) new_value, connect_by_isleaf isleaf
              from (select lvl
                         , substr(line,1,instr(line,'*')-1)||
                             translate(substr(line,instr(line,'*'))
                                      ,'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
                                      ,(select translate_chars from t)) r_line
                    from (select level lvl
                               , regexp_substr(edi_value,'^.*$',1,level,'m') line
                          from (select table1.edi_value from dual)
                          connect by level <= regexp_count(edi_value,'^.*$',1,'m')))
              start with lvl=1 connect by lvl=(prior lvl)+1)
        where isleaf=1)
from table1;

| (SELECTNEW_VALUEFROM(SELECTSUBSTR(SYS_CONNECT_BY_PATH(R_LINE,''),2)NEW_VALUE,CONNECT_BY_ISLEAFISLEAFFROM(SELECTLVL,SUBSTR(LINE,1,INSTR(LINE,'*')-1)||TRANSLATE(SUBSTR(LINE,INSTR(LINE,'*')),'0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ',(SELECTTRANSLATE_CHARSFR |
| :---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| ISA*66*          *66*          *67*1935006626     *VV*098532471      *650902*6763*K*66360*666613640*6*P*>~<br>    GS*GZ*3084295877*098532471*96650902*6763*13640*I*663606~<br>    ST*795*690816660*663606I072G0~                                            |
| ISA*66*          *66*          *67*32471742247     *VV*098532471      *650902*6763*K*66360*666613640*6*P*>~<br>    GS*GZ*3084295877*098532471*96650902*6763*13640*I*663606~<br>    ST*795*690816660*663606I072G                                             |

db<>fiddle here

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

3 Comments

This works great! If I want to change it so that it does not change values for TWO (2) * for each change, which code should I change? For ex. ST*824* should remain as is, and ONLY the next part getting changed?
instr has two optional parameters, position and occurrence for that sort of thing so that's a minor tweak: dbfiddle.uk/…
Are you on Skype?
0

You can use CTE's with a CONNECT to generate the strings for the letters and numbers.

Then use the ordered and scrambled strings in the translate.

A CROSS APPLY can be used to REGEX split the message into parts.
Then only translate those that start with a *.
And use LISTAGG to glue the parts back together. 

WITH 
NUMS as
(
  select 
  LISTAGG(n, '') WITHIN GROUP (ORDER BY n) as n_from,
  LISTAGG(n, '') WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE) as n_to
  from (select level-1 n from dual connect by level <= 10) 
),
LETTERS as
(
  select 
  LISTAGG(c, '') WITHIN GROUP (ORDER BY c) as c_from,
  LISTAGG(c, '') WITHIN GROUP (ORDER BY DBMS_RANDOM.VALUE) as c_to
  from (select chr(ascii('A')+level-1 ) c from dual connect by level <= 26) 
)
SELECT ca.scrambled as scrambled_message
FROM table1 t
CROSS JOIN NUMS
CROSS JOIN LETTERS
CROSS APPLY 
(
 SELECT LISTAGG(CASE WHEN part like '*%' then translate(part, n_from||c_from, n_to||c_to) else part end, '') WITHIN GROUP (ORDER BY lvl) as scrambled
 FROM
 (
  SELECT 
  level AS lvl,
  REGEXP_SUBSTR(t.edi_message,'[*]\S+|[^*]+',1,level,'m') AS part
  FROM dual
  CONNECT BY level <= regexp_count(t.edi_message, '[*]\S+|[^*]+')+1
 ) parts
) ca;

A test on db<>fiddle here

Example output:

SCRAMBLED_MESSAGE
-----------------------------------------------------------------------------------------------------------
ISA*99*          *99*          *92*3525999959     *PP*950525023      *959595*9292*A*99299*999932909*9*J*>~
    GS*WQ*2900555022*950525023*59959595*9292*32909*I*992999~
    ST*255*959039999*992999I925V9~
ISA*99*          *99*          *92*25023205502     *PP*950525023      *959595*9292*A*99299*999932909*9*J*>~
    GS*WQ*2900555022*950525023*59959595*9292*32909*I*992999~
    ST*255*959039999*992999I925W9~
Improve this answer

6 Comments

really neat way of generating the translate strings. You can just concatenate them rather than nesting translates can't you?
like this I mean: dbfiddle.uk/…
Omg, you make a good point about concatinating them. (why didn't I see the obvious!) Thanks.
It changes line 2 and line 3 in the same row. I mean GS, ST should remain as is and not change.
Oh like that. Not just the start of the varchar, but also after newlines in the string. Hmm, that's gonna be a tad harder to do.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.