Replacing String from each row of the column in oracle sql select

1
votes

Running following sql select query on user_tab_partition on oracle 11g

{ select high_value from user_tab_partitions where table_name='TAB1'; }

gives following results..

TO_DATE(' 2013-02-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TO_DATE(' 2013-02-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TO_DATE(' 2013-02-17 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TO_DATE(' 2013-02-24 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TO_DATE(' 2013-03-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN') TO_DATE(' 2013-03-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')

Question : how to remove , 'NLS_CALENDAR=GREGORIAN' from each row using replace or similar function .?

1
sqloracle11g

1 Answers

0
votes

Try this:

select regexp_replace(high_value, ',\s*''NLS_CALENDAR=GREGORIAN''', '')
from user_tab_partitions 
where table_name='TAB1';

Single quotes are escaped in Oracle SQL by typing them twice, even in RegEx patterns.