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
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.