Need to extract everything before/after substring in Oracle SQL

Question

I have a field, FARMBOL, that has information that I need to split. At some point in the field, it will say "IMPORTER:" followed by some more information.

I.E., "20394823409 IMPORTER: John Doe"

I need to extract the "20394823409 " as one field, and the "IMPORTER: John Doe" as another. How can I do this? The code is part of a query in SSRS on an Oracle DB.

What is the logic that you want to implement? Read up to the first space for the first column and everything after the first space as the second? Read up to the first non-numeric character? Everything before the first alphabetic character for the first column? — Justin Cave, Jun 14 '16 at 18:22
Read up until the "IMPORTER" portion of the string as column 1, and "IMPORTER" and everything after as column 2. — Dom Vito, Jun 14 '16 at 18:23

Alex Poole · Accepted Answer · 2016-06-14T18:32:24.380

If it will really always say 'IMPORTER:' then you can use substr() to get the sub-strings, and instr() to figure out how much to get:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as part1,
  substr(farmbol, instr(farmbol, 'IMPORTER:')) as part2
from t;

PART1        PART2            
------------ ------------------
20394823409  IMPORTER: John Doe

You can manipulate the extracted values, e.g. trimming the trailing space from the first part, or converting it to a number if it always will be:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as part1,
  trim(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1)) as part1_trim,
  cast(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as number) as part1_num,
  substr(farmbol, instr(farmbol, 'IMPORTER:')) as part2
from t;

PART1        PART1_TRIM     PART1_NUM PART2            
------------ ----------- ------------ ------------------
20394823409  20394823409  20394823409 IMPORTER: John Doe

And if you don't really want to keep the 'IMPORTER:' part of the string you can adjust the starting position of the second substring, by the length of that fixed value:

with t (farmbol) as (select '20394823409 IMPORTER: John Doe' from dual)
select cast(substr(farmbol, 1, instr(farmbol, 'IMPORTER:') - 1) as number) as part1_num,
  trim(substr(farmbol, instr(farmbol, 'IMPORTER:') + 9)) as importer
from t;

   PART1_NUM IMPORTER
------------ --------
 20394823409 John Doe

If you needed a more flexible pattern then you could use regular expressions instead, but that seems like overkill here.

Having to do this suggests you should really be storing this data in separate columns to start with, possibly with different data types, rather than mashed together in a single string column.

Need to extract everything before/after substring in Oracle SQL

1 Answers1