oracle 12 - select string after last occurrence of a character-CodePudding

I am currently using the UPS API to validate addresses and in our system the max length the address line 1 is 35 characters and when its over 35 characters it doesn't update our database because of the length issue.

so what I want to be able to do is if the length of the address is > 35 then substring only 35 characters but not breaking the flow of the address so for example if the address is

10620 SOUTHERN HIGHLANDS PKWY # 110-811

as you can see for this example "10620 SOUTHERN HIGHLANDS PKWY # 110" is exactly 35 characters but I dont want to break the "110-811" part so I want to go back to the last space and then put the rest of the address on address line 2

I would like it to return

address 1: 10620 SOUTHERN HIGHLANDS PKWY #
address 2: 110-811

CodePudding user response：

Something like this could work:

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;

CodePudding user response：

For complicated string manipulation, I would recommend making pl/sql functions, so that you can apply procedural programming.

In general, you cannot rely on the last space for the line break, as the second line may have a number of spaces too.

You will need to split the string in words, and collect words for the first line, not exceeding 35 chars. The remaining words are for line 2. You will need an array, a for-loop, and some if's. We switched to PostgreSQL where you can write functions in a choice of languages, also python, but in Oracle, plain pl/sql will work too.

CodePudding user response：

Here's one option; read comments within code. As you said that address can have max 2 parts (35 characters in length), function accepts whole address as the 1st parameter and address part (1 or 2) as the 2nd parameter.

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.

Testing:

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>