0

There's a reference field that concatenates multiple different columns. I'm looking to extract the values between the last caret and the = special characters. There's no fixed length between these values so it could range between 2 characters and 100

I understand we can use Substr and Instr however, I'm struggling to get it to work as I expect.

Example of string:

CONTRACT=30TEE^MCH_CODE=01.01 THIS IS A TEST^
CONTRACT=30TEE^MCH_CODE=01.01 THIS IS A TEST4545^
CONTRACT=30TEE^MCH_CODE=01.01 Testing^
CONTRACT=30TEE^MCH_CODE=01.01 This is an example45^

Expected Output:

01.01 THIS IS A TEST
01.01 THIS IS A TEST4545
01.01 Testing
01.01 This is an example45
PAPERCLIPSANDPAPER
  • 77
  • 1
  • 1
  • 10

1 Answers1

1

There are many ways to do this; this is one, without regular expressions:

SQL> with test(x) as
  2  (
  3      select 'CONTRACT=30TEE^MCH_CODE=01.01 THIS IS A TEST^' from dual union all
  4      select 'CONTRACT=30TEE^MCH_CODE=01.01 THIS IS A TEST4545^' from dual union all
  5      select 'CONTRACT=30TEE^MCH_CODE=01.01 Testing^' from dual union all
  6      select 'CONTRACT=30TEE^MCH_CODE=01.01 This is an example45^' from dual
  7  )
  8  select trim('^' from reverse(substr(reverse(x), 1, instr(reverse(x), '=') -1))) x2
  9  from test;

X2
------------------------------
01.01 THIS IS A TEST
01.01 THIS IS A TEST4545
01.01 Testing
01.01 This is an example45

How it works: reverse simply gives the string in reverse order, so that searching for the last '=' becames searching for the first one in the reverse string; once you know the position of the last/first '=', substr does the job and you only need to remove the last character with trim.

With regular expressions (more compact but slower) you could use:

regexp_substr(x, '([^=]+)\^$', 1, 1, '', 1)

Notice that these are based on the assumption that the last '^' is the last character of the string.

Aleksej
  • 22,443
  • 5
  • 33
  • 38