Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace() function help
regexp_replace() function help [message #666887] Thu, 30 November 2017 07:51 Go to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
select 'MUNI_ID,REGION_NUM,COUNTY_ID,COUNTY_NAME,MUNI_NAME,MUNI_TYPE_ID,MUNI_TYPE_NAME,MUNI_OSC_CLASS,MOU_TYPE,MOU_DESC,REDC_TYPE,REDC_DESC,2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP' str
  from dual

expected output:
,,,,,,,,,,,,Allotment,,,Payment,,

the character 2015_CA is to be replaced by the Allotment and character 2015_CP is to be replaced by Payment. these characters doesn't have fixed position. i thought of maybe the regexp_replace() function can be used for this scenario.

this question might arise:
why format in comma? this will be used for comma delimited file (*.csv) as the 1st column header.


thanks.
Re: regexp_replace() function help [message #666889 is a reply to message #666887] Thu, 30 November 2017 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
I think you've over-simplified that a few steps too many.
I can't see any relationship between the select and the expected output.
Re: regexp_replace() function help [message #666890 is a reply to message #666889] Thu, 30 November 2017 08:44 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
yes the data was already been simplified in the table for reporting.
Re: regexp_replace() function help [message #666891 is a reply to message #666889] Thu, 30 November 2017 08:56 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
When I say:
cookiemonster wrote on Thu, 30 November 2017 14:18

I can't see any relationship between the select and the expected output.
That means you need to explain the relationship.
I've got no idea what data, in what form, you are trying to modify to get the expected result.
Without knowing that (or even having a rough idea) it is impossible to make suggestions.
Re: regexp_replace() function help [message #666895 is a reply to message #666891] Thu, 30 November 2017 09:15 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
'MUNI_ID,REGION_NUM,COUNTY_ID,COUNTY_NAME,MUNI_NAME,MUNI_TYPE_ID,MUNI_TYPE_NAME,MUNI_OSC_CLASS,MOU_TYPE,MOU_DESC,REDC_TYPE,REDC_DESC,2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP' 

the data above will be used for reporting formatted as comma delimited file as excel spreadsheet. to begin with the report needs a header then followed by the column names and the data.

header
column names
values

in the data example above the first occurrence of the character _CA in this case it is the 2015_CA will be replaced by Alloment. and the first occurrence of character _CP in this case 2015_CP will be replaced by Payment. then any others will be null but it has to retain the comma. this is to create the header line and the output will be:
,,,,,,,,,,,,Allotment,,,Payment,,

note that the position is not fixed.

when the file is opened in excel it will have the header for example:
                                                  Allotment                    Payment

this is more likely a string manipulation it does not need any relationship because only one line is needed to build the header line.

i attached a sample screen shot. hope this helps.

/foru/forum/fa/13712/0/


[Updated on: Thu, 30 November 2017 12:59]

Report message to a moderator

Re: regexp_replace() function help [message #666910 is a reply to message #666887] Thu, 30 November 2017 19:11 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9077
Registered: November 2002
Location: California, USA
Senior Member
The following just answers the original question, without regard to its purpose. It separates the strings between commas, makes the replacements, then concatenates them together again.

SCOTT@orcl_12.1.0.2.0> with test_data as
  2    (select 'MUNI_ID,REGION_NUM,COUNTY_ID,COUNTY_NAME,MUNI_NAME,MUNI_TYPE_ID,MUNI_TYPE_NAME,MUNI_OSC_CLASS,MOU_TYPE,MOU_DESC,REDC_TYPE,REDC_DESC,2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP' str
  3  	from   dual)
  4  select listagg (strings, '') within group (order by rn)
  5  from   (select rownum rn,
  6  		    decode
  7  		      (regexp_substr (str, '[^,]+', 1, rownum),
  8  		       '2015_CA', 'Allotment',
  9  		       '2015_CP', 'Payment',
 10  		       ',') strings
 11  	     from   test_data
 12  	     connect by level <= regexp_count (str, ',') + 1)
 13  /

LISTAGG(STRINGS,'')WITHINGROUP(ORDERBYRN)
--------------------------------------------------------------------------------
,,,,,,,,,,,,Allotment,,Payment,,

1 row selected.
Re: regexp_replace() function help [message #666924 is a reply to message #666887] Fri, 01 December 2017 07:35 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
Assuming first _CA in string always occurs before first _CP:

with t1 as (
            select 'MUNI_ID,REGION_NUM,COUNTY_ID,COUNTY_NAME,MUNI_NAME,MUNI_TYPE_ID,MUNI_TYPE_NAME,MUNI_OSC_CLASS,MOU_TYPE,MOU_DESC,REDC_TYPE,REDC_DESC,2015_CA,2016_CA,2017_CA,2015_CP,2016_CP,2017_CP' str
              from  dual
           ),
     t2 as (
            select  str,
                    instr(str || ',','_CA,') first_ca_pos,
                    instr(str || ',','_CP,') first_cp_pos
              from  t1
           )
select  regexp_replace(substr(str,1,first_ca_pos),'[^,]') || 'Allotment' ||
        regexp_replace(substr(str,first_ca_pos,first_cp_pos - first_ca_pos),'[^,]') ||
        'Payment' || regexp_replace(substr(str,first_cp_pos),'[^,]') new_str
  from  t2
/

NEW_STR
--------------------------------
,,,,,,,,,,,,Allotment,,,Payment,,

SQL> 

If not, all it needs is some tweaking using LEAST/GREATEST.

SY.

[Updated on: Fri, 01 December 2017 07:38]

Report message to a moderator

Re: regexp_replace() function help [message #666925 is a reply to message #666910] Fri, 01 December 2017 08:51 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
great thanks to much this helps.
Re: regexp_replace() function help [message #666926 is a reply to message #666924] Fri, 01 December 2017 08:53 Go to previous messageGo to next message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
perfect thanks so much.
Re: regexp_replace() function help [message #666927 is a reply to message #666926] Fri, 01 December 2017 08:57 Go to previous message
wtolentino
Messages: 390
Registered: March 2005
Senior Member
i actually created a function below it works but Solomon's example is what i exactly needed so i do not have to use a function and just simple straight SQL.

  function fnc_build_report_header (pColData varchar2, pStartYear varchar2) return varchar2 is
    vHdrLn   varchar2(4000);
    vYearCA  varchar2(40);
    vYearCP  varchar2(40);
  begin

    vYearCA := pStartYear||'_CA';
    vYearCP := pStartYear||'_CP';

    select --listagg(col_name,',') within group(order by rn)  col_name
           regexp_replace((listagg(col_name,',') within group(order by rn)),'[x]+') col_name
      into vHdrLn
      from (select decode(iv2.col_name,vYearCA,'Alloment',
                                       vYearCP,'Payment','x') col_name,
                   iv2.rn
              from (select rowid rid,
                           level comma_pos,
                           regexp_substr(iv1.str,'[^,]+',1,level) col_name,
                           row_number() over (partition by rowid order by level) rn
                      from (select pColData str from dual) iv1
                    connect by rowid = prior rowid
                        and prior sys_guid() is not null
                        and level <= regexp_count(iv1.str,',') + 1) iv2) iv3;     


    return (vHdrLn);
  end fnc_build_report_header;  
Previous Topic: Function Creation
Next Topic: Difference in SQL 'order by' and MIN, MAX, LISTAGG results
Goto Forum:
  


Current Time: Fri Mar 29 08:50:41 CDT 2024