0

I need a way to trim a string in PL/SQL based on the location of the last commas in the string. However, there is no uniform format for the incoming strings, and I can't find a way to trim the string effectively.

HU-15-02 | HU, NYI, HAA East (should be trimmed to just HAA East)  
MX-01-05 | MX, 01-05, OFFICES (OFFICES)
DK-94-02 | DK, ViewCom   (VIEWCOM)

the format is country code, followed by a building ID (if applicable), followed by the name of the building (which is what I want)

Angus
  • 1
  • Split on space, and take the last element in the resulting array. See http://stackoverflow.com/questions/3710589/is-there-a-function-to-split-a-string-in-plsql – Robert Harvey Oct 29 '15 at 15:05

2 Answers2

0

Get the location of last comma by counting back from the end of the string and then trim from the comma plus space forward

select substr(your_text,INSTR(your_text,',',-1) +2)
from your_table;
kevinskio
  • 4,431
  • 1
  • 22
  • 36
0

REGEXP_SUBSTR() to the rescue:

SQL> with tbl(str) as (
     select 'HU, NYI, HAA East' from dual
     union
     select 'MX-01-05 | MX, 01-05, OFFICES' from dual
     union
     select 'DK-94-02 | DK, ViewCom' from dual
   )
   select regexp_substr(str, '^.*, (.*)$', 1, 1, null, 1) bldg_name
   from tbl;

BLDG_NAME
-----------------------------
ViewCom
HAA East
OFFICES

SQL>
Gary_W
  • 9,933
  • 1
  • 22
  • 40