Home » SQL & PL/SQL » SQL & PL/SQL » regexp_replace() function help
regexp_replace() function help [message #666887] |
Thu, 30 November 2017 07:51 |
wtolentino
Messages: 400 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 #666891 is a reply to message #666889] |
Thu, 30 November 2017 08:56 |
cookiemonster
Messages: 13922 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 |
wtolentino
Messages: 400 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:
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.
[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 |
|
Barbara Boehmer
Messages: 9092 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 |
Solomon Yakobson
Messages: 3273 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 #666927 is a reply to message #666926] |
Fri, 01 December 2017 08:57 |
wtolentino
Messages: 400 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;
|
|
|
Goto Forum:
Current Time: Sat May 11 05:53:38 CDT 2024
|