Oracle 11g split the string and it will use in Update statement

Question

I don't know how to split a string in Oracle 11g.

My input input string is the manufacturer name.

If the Manufacturer’s Name is longer than 30 characters

Find the occurrence of first occurrence of an open parenthesis – “(“
If found Find the next occurrence of a close parenthesis – “)”
Replace the last characters of the Manufacturer’s Name with the cage code characters
If NOT found
Truncate the Manufacturer’s Name to 30 characters

Example:
Input :
Arrow Industries International-MX7(4432)
Output:
Arrow Industries Interna(4432)

the update statement should be

update manufacture_table 
  set name='Arrow Industries Interna(4432)'

please help on this

Will it always be true that the "cage code characters" will be 30 or fewer? If not, what is the requirement? — , May 20 '17 at 16:14

LukStorms · Answer 1 · 2017-05-21T06:15:40.213

The regex in the example puts the first 24 characters and the 6 characters of the cage code into capture group 1 & 2.

So by replacing it with \1\2 you loose the characters in between.

The substring ensures that the result won't be over 30, even if no replacement was done.

And thanks to the CASE, if it's not longer than 30, then output=input.

 select input,
(case 
 when length(input) > 30 then substr(REGEXP_REPLACE(input, '^(.{24}).*(\(\w{4}\))', '\1\2'),1,30)
 else input
 end) as output
from (
    select 'Arrow Industries International-MX7(4432)' as input from dual
    union all
    select 'Wooden BowIndustries International-WBC' from dual
) q

If the length of the cage code is variable, then it becomes a bit more complicated. Since we need to calculate the length of the substring to add to the cage code:

select input,
(case 
 when length(input) > 30 
 then substr(input,1,30-coalesce(length(REGEXP_SUBSTR(input,'\(\w{1,28}\)')),0)) || REGEXP_SUBSTR(input,'\(\w{1,28}\)')
 else input
 end) as output
from (
    select 'Arrow Industries International-MX7(1234567)' as input from dual
    union all
    select 'Bow Industries International-BII(a12)' from dual
) q

To use it in the update:

update manufacture_table 
set name=substr(name,1,30-coalesce(length(REGEXP_SUBSTR(name,'\(\w{1,28}\)')),0)) || REGEXP_SUBSTR(name,'\(\w{1,28}\)')
where length(name)>30

score 0 · Answer 2 · 2017-05-21T14:56:19.697

Setup:

create table manufacture_table(name varchar2(80));

insert into manufacture_table values ('International Business Machines Corp.');
insert into manufacture_table values ('Arrow Industries International-MX7(4432)');
insert into manufacture_table values ('Extra Company');
insert into manufacture_table values ('My Business (33042)');
insert into manufacture_table values 
          ('Very long name for a company, really very long (0123456789012345678901234)');
insert into manufacture_table values
          ('Company name-(01234567890123456789012345678901234567)');

commit;
select name, length(name) as len from manufacture_table;

NAME                                                                                  LEN
-------------------------------------------------------------------------------- --------
International Business Machines Corp.                                                  37
Arrow Industries International-MX7(4432)                                               40
Extra Company                                                                          13
My Business (33042)                                                                    19
Very long name for a company, really very long (0123456789012345678901234)             74
Company name-(01234567890123456789012345678901234567)                                  53

 6 rows selected

This type of problem is often best solved with a MERGE statement. Some of the prep work is done in a subquery and the rest in the UPDATE clause of the MERGE statement.

merge into manufacture_table t
  using (
         select rowid                          as rn,
                regexp_substr(name, '[^(]*')   as init_str,
                regexp_substr(name, '\(.*?\)') as cage
         from   manufacture_table
        ) q
        on (q.rn = t.rowid)
when matched then update
    set t.name = case when q.cage is null       then substr(q.init_str, 1, 30)
                      when length(q.cage) >= 30 then substr(q.cage, 1, 30)
                      else substr(q.init_str, 1, 30 - length(q.cage)) || q.cage   end
where length(t.name) > 30
;

4 rows merged.

select name, length(name) as len from manufacture_table;

NAME                              LEN
------------------------------   ----
International Business Machine     30
Arrow Industries Interna(4432)     30
Extra Company                      13
My Business (33042)                19
Ver(0123456789012345678901234)     30
(01234567890123456789012345678     30

I included several "exceptional cases" in my sample data, to see how they are handled. There are, however, many others, and your requirement needs to specify what should happen in each case (or it needs to specify they are not possible). For example, what if a company name has a pair of parentheses in the name, from positions 13 to 20, and then the name continues past the closing parenthesis, but the total name is 40 characters long? I did not attempt to handle such cases in my solution since I don't know what the desired output would be.

Oracle 11g split the string and it will use in Update statement

2 Answers2