这里有一个选择读取代码中的注解。正如您所说，地址最多可以有2个部分（长度为35个字符），函数接受整个地址作为第一个参数，地址部分（1或2）作为第二个参数。

SQL> create or replace function f_test (par_string in varchar2, par_part in number)
  2    return varchar2
  3  is
  4    /* This code presumes that PAR_STRING won't be longer than 70 characters
  5    */
  6    l_str varchar2(35);
  7    l_pos number;
  8    retval varchar2(35);
  9  begin
 10    -- if PAR_STRING is up to 35 characters in length, return it as the first part
 11    -- of address / null for the second part
 12    if length(par_string) <= 35 then
 13       if par_part = 1 then
 14          retval := par_string;
 15       elsif par_part = 2 then
 16          retval := null;
 17       end if;
 18    else
 19       -- PAR_STRING is longer than 35 characters - split it in two, depending
 20       -- on which part of address you want
 21       if par_part = 1 then
 22          -- first part: return substring from 1st position up to first space that
 23          -- precedes 35th position
 24          l_str := substr(par_string, 1, 35);
 25          l_pos := instr(l_str, ' ', -1, 1);
 26          retval := substr(l_str, 1, l_pos);
 27       elsif par_part = 2 then
 28          -- second part: return substring from position of the first space that
 29          -- precedes 35th position, up to total length of the string
 30          l_str := substr(par_string, 1, 35);
 31          l_pos := instr(l_str, ' ', -1, 1);
 32
 33          l_str := substr(par_string, l_pos + 1);
 34          retval := l_str;
 35       end if;
 36    end if;
 37
 38    return retval;
 39  end f_test;
 40  /

Function created.

测试：

SQL> select f_test('10620 SOUTHERN HIGHLANDS PKWY # 110-811', 1) address_1,
  2         f_test('10620 SOUTHERN HIGHLANDS PKWY # 110-811', 2) address_2
  3  from dual;

ADDRESS_1                                ADDRESS_2
---------------------------------------- --------------------
10620 SOUTHERN HIGHLANDS PKWY #          110-811

SQL> select f_test('12345 SHORT ADDRESS # 123-456', 1) address_1,
  2         f_test('12345 SHORT ADDRESS # 123-456', 2) address_2
  3  from dual;

ADDRESS_1                                ADDRESS_2
---------------------------------------- --------------------
12345 SHORT ADDRESS # 123-456

SQL>

像这样的事情可能会起作用：

WITH dat AS
(
  SELECT '10620 SOUTHERN HIGHLANDS PKWY # 110-811' AS addr FROM dual
)
SELECT SUBSTR(addr,0,INSTR(SUBSTR(addr,0,35), ' ', -1)) --address line 1
     , SUBSTR(addr,INSTR(SUBSTR(addr,0,35), ' ', -1))   --address line 2
  FROM dat;

对于复杂的字符串操作，我会建议制作pl/sql函数，这样您就可以应用过程化编程。
通常，您不能依赖最后一个空格作为换行符，因为第二行也可能有许多空格。
你需要将字符串拆分成单词，并收集第一行的单词，不超过35个字符。剩下的单词用于第二行。你需要一个数组，一个for循环，和一些if。我们切换到PostgreSQL，在那里你可以用多种语言编写函数，也可以用python，但在Oracle中，普通的pl/sql也可以工作。