SQL & PL/SQL

Can be be more specific? Data you posted does not have any generalized format.

There is no function to get the country in these three forms of data.

U can use substr and instr for each type of dataline

data = 'CITY';'ZIP';'COUNTRY'

use: substr(instr(data, ';', 1, 2)+1)

data = ;;'CITY';'ZIP';'COUNTRY'

use: substr(instr(data, ';', 1, 4)+1)

data = ;;;'COUNTRY'

use: substr(instr(data, ';', 1, 3)+1)

That's the problem..there's no specific pattern except i need to get the last part of the string which is 'COUNTRY' till it faces a semicolon ?
Is there some way to parse the string backwards like get the length and then parse it starting from length backwards ?

Thanks

Something like this:

SQL> with t as (
  2  select q'*'CITY';'ZIP';'COUNTRY'*' c1 from dual union all
  3  select q'*;;'CITY';'ZIP';'COUNTRY'*' c1 from dual union all
  4  select q'*;;;'COUNTRY'*' c1 from dual
  5  )
  6  select c1,substr(c1,instr(c1,';',-1,1)+1) from t
  7  ;

C1                       SUBSTR(C1,INSTR(C1,';',-
------------------------ ------------------------
'CITY';'ZIP';'COUNTRY'   'COUNTRY'
;;'CITY';'ZIP';'COUNTRY' 'COUNTRY'
;;;'COUNTRY'             'COUNTRY'

this?

SQL> ed
Wrote file afiedt.buf

  1  WITH T AS
  2   (SELECT 'CITY'||';'||'ZIP'||';'||'COUNTRY' str FROM dual
  3      UNION ALL
  4   SELECT ';'||';'||'CITY'||';'||'ZIP'||';'||'COUNTRY' FROM dual)
  5  ---- end of data
  6  SELECT str,SUBSTR(str,INSTR(str,';',-1,1)+1)
  7* FROM T
SQL> /

STR                SUBSTR(STR,INSTR(S
------------------ ------------------
CITY;ZIP;COUNTRY   COUNTRY
;;CITY;ZIP;COUNTRY COUNTRY

same as Nunez
Message was edited by:
devmiral

Or in 10g, using regular expressions:

WITH t AS (SELECT q'*'CITY';'ZIP';'COUNTRY'*' c1 
             FROM dual 
            UNION ALL 
           SELECT q'*;;'CITY';'ZIP';'COUNTRY'*' 
             FROM dual 
            UNION ALL 
           SELECT q'*;;;'COUNTRY'*'
             FROM dual
           ) 
SELECT c1
     , SUBSTR(c1, INSTR(c1, ';', -1, 1) + 1) new_c1
     , REGEXP_REPLACE(c1, '^.*;') new_c1_regex 
  FROM t
;

C1                       NEW_C1                   NEW_C1_REGEX
------------------------ ------------------------ ------------------------
'CITY';'ZIP';'COUNTRY'   'COUNTRY'                'COUNTRY'
;;'CITY';'ZIP';'COUNTRY' 'COUNTRY'                'COUNTRY'
;;;'COUNTRY'             'COUNTRY'                'COUNTRY'

C.

Like this?

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Oct 9 12:31:33 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SQL> set linesize 100
SQL> with t as (
  2      select 'CITY;ZIP;COUNTRY' c1 from dual union all
  3      select ';;CITY;ZIP;COUNTRY' from dual union all
  4      select ';;CITY;ZIP;' from dual union all
  5      select ';;;COUNTRY' from dual
  6      )
  7  --
  8  Select 
  9   c1, 
 10   substr(c1,decode(instr(c1,'COUNTRY'),0,length(c1)+1,instr(c1,'COUNTRY')),7) 
 11  from t;

C1                 SUBSTR(
------------------ -------
CITY;ZIP;COUNTRY   COUNTRY
;;CITY;ZIP;COUNTRY COUNTRY
;;CITY;ZIP;
;;;COUNTRY         COUNTRY

SQL> 

col c1 for a30
col new_c1 for a10
col new_c1_regex1 for a15
col new_c1_regex2 for a15

WITH t AS (SELECT q'['CITY';'ZIP';'COUNTRY']' c1 FROM dual 
UNION SELECT q'[;;'CITY';'ZIP';'COUNTRY']' FROM dual 
UNION SELECT q'[;;;'COUNTRY']'FROM dual) 
SELECT c1
, SUBSTR(c1, INSTR(c1, ';', -1, 1) + 1) new_c1
, REGEXP_REPLACE(c1, '^.*;') new_c1_regex1
, REGEXP_substr(c1 , '[^;]*$') new_c1_regex2
FROM t;