Search code examples
db2azure-ad-b2cdb2-luw

would REGEXP or TRANSLATE in DB2 another choice for REPLACE?

What would be the best way to shorten below SQL code?

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TRIM(MYFIELD),'-R1',''),'-R2',''),'-R3',''),'-R4',''),'-R5',''),'-R6',''),'-R7',''),'-R8',''),'-R9',''),'-RA',''),'-RB',''),'-RC',''),'-RD',''),'-RE',''),'-RF','') AS TESTFIELD

here is what I have tried:

    REGEXP_REPLACE(MYFIELD,'-R[0-100][a-fA-F]','')

Original Data

  1. N-RX ABCD
  2. GROUP OPTION -01
  3. ADVANTAGE 65 SELECT B-R11
  4. ADVANTAGE 65 SELECT B-RA
  5. ADVANTAGE 65 SELECT B-R09
  6. ADVANTAGE 65 SELECT B-RB
  7. ADVANTAGE 65 SELECT B/2A

Result Needed:

  1. N-RX ABCD
  2. GROUP OPTION -01
  3. ADVANTAGE 65 SELECT B

Solution:

REGEXP_REPLACE(Trim(MyField), '[-|/]R[0-9a-zA-Z*][0-9a-zA-Z*]*$', '')
Solution

  • Your regular expression is your current issue. Try something like:

    REGEXP_REPLACE(DACL_PDLV_5_DE, '-R[0-9a-fA-F][0-9]*$', '')

    This matches '-R' followed by a digit or a-f or A-F, optionally followed by another digit, but only at the end of the string.

    If you could have a two-digit hex value you will want to adjust accordingly.