Search code examples
sqlregexoracle-databasesubstr

Using Regex_substr in Oracle to select string up to the last occurence of a space within \n characters length

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 -

  1. select a string UP TO the last space within 40 characters - for column_a
  2. select a string AFTER the last space within 40 characters - for column_b

These are the case statements that I have so far with substr:

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 am not familiar with regular expressions at all and so any help would be very much appreciated!

Solution

  • 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