We have an issue where a column in our Oracle database has a longer character length than a field in another system.
Therefore I am trying to use case statements along with substr in order to split strings that are more than 40 characters in length. My case statements so far do what I want them to do in the fact that it leaves the first 40 characters of a string in column_a and then puts the remainder of the string in column_b.
However, the problem that I have is that by just using substr, the strings are being split midway through words.
So I was wondering if anybody knew of a couple of regular expressions that I could use with regex_substr that will -
CASE WHEN Length(column_a) > 10 THEN SubStr(column_a, 0, 40) END AS column_a,
CASE WHEN Length(column_a) > 40 THEN SubStr(addressnum, 41) END AS column_b
I've solved with instr/substr:
select substr(column_a,1,instr(substr(column_a,1,40), ' ', -1 )) column1, substr(column_a,instr(substr(column_a,1,40), ' ', -1 )+1, 40) column2 from table1