PL/SQL :: How To Use REGEXP_REPLACE To Remove ORACODES
Dec 27, 2012
Basically I need to use REGEXP_REPLACE in order to remove from a string certain oracle code pricislly those which are used on RAISE_APPLICATION_ERROR from 20000 to 20999. We have an application here that handles those exceptions and each exception append each other so inside them we can find those ORA-20001: Some error. ]
So I was wondering if I can achieve that by using REGEXP_REPLACE. So which regexp I could use so that I can remove those oracode only!
View 6 Replies
ADVERTISEMENT
Jul 14, 2010
I've been tasked to parse tags from a string that look like the following:
{Date + XXX}
where XXX represents a numeric value. I have to replace this, including the brace characters with
SYSDATE + XXX
which will ultimately calculate SYSDATE plus the number of days suggested by XXX. The problem is that I am trying to use regexp_replace to achieve this goal but since XXX is completely arbitrary, I cannot search for it as a fixed value. So, ultimately, I would like to use a regular expression that ignores the numeric part of my search and only replaces the starting brace, the "Date + " part and the ending brace, leaving the numeric portion intact. I was trying to do something like the following
myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below
myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));
matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...
View 14 Replies
View Related
May 23, 2013
I am looking for the best fastest to process by searching string and replace values. I am process many rows like 200k,
search for strings starting with "S" or "M" and replace only those letters with NULL eg S8795371 becomes 8795371, and M4454332 becomes 4454332 however other string values shouldn't be affected only if it starts with S/M. i was thinking of using REGEXP_REPLACE and what is the best way to construct this statement?
View 5 Replies
View Related
Sep 11, 2013
I'm trying to eliminate duplicate string for more than 1 occurrences along with its delimiters, but couldn't get it working. Here is what I tried.
SQL> column str format a30
SQL> column replaced format a30
SQL> with x as
2 (select 'a#~#b#~#a#~#d' as str from dual union all
3 select 'a#~#b#~#c#~#a' as str from dual union all
4 select 'b#~#a#~#c#~#a' as str from dual)
select str,
regexp_replace(str, '[^a|#~#a]{2,}','',1,2) replaced
from x; 5 6 7
STR REPLACED
------------------------------ ------------------------------
a#~#b#~#a#~#d a#~#b#~#a#~#d
a#~#b#~#c#~#a a#~#b#~#c#~#a
b#~#a#~#c#~#a b#~#a#~#c#~#a
The output I need is
a#~#b#~#d
a#~#b#~#c
b#~#a#~#c
View 12 Replies
View Related
Feb 9, 2011
Any alternative to filer the Initial using a single REGEXP_REPLACE ?
Oracle version 10.2.xxxx
Currently using two REGEXP_REPLACEs.
SELECT REGEXP_REPLACE(
REGEXP_REPLACE('K I Rajuvan K I', '(([[:upper:]]{1,2})) ','')
, '([[:upper:]]{1,2})$','') CITY
FROM dual;
View 8 Replies
View Related
Jul 11, 2013
I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...?
select REGEXP_REPLACE(dbms_metadata.get_ddl('TABLE','EMP'),'("Scott".)','', 1, 0, 'i') from dual; result: CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "SAL" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGTABLESPACE "USERS"I want:CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "SAL" NUMBER(7,2), "DEPTNO" NUMBER(2,0))
View 18 Replies
View Related
Sep 24, 2012
I have table of 60 gb(indexes 60gb ) and it is subject to fragmentation around 10gb .
I m going to remove fragmentation . As i know i have three options
1.expdp/impdp .
2.CTAS (create table ........as.......) with parallel option .
3.Moving table into another tablespace with parallel option .
I have 4 physical and 4 logical cores(total 8 cores) on server .
View 1 Replies
View Related
Jun 3, 2010
I receive source data with leading zeros. The Rules says: "If there is a leading 0 it has to be removed and data has to be shown starting with character 2".Here is my code I started with:
CREATE TABLE leading_0_test
(
col_1 VARCHAR2(10)
)
insert into leading_0_test values('00123')
select trim(leading'0' from COL_1),trim(leading'0' from substr(COL_1,1,1)),COL_1 from leading_0_test
The first trim removes all leading zeros. Therefore I tried to solve it with the substr, but without success.
View 7 Replies
View Related
Oct 20, 2010
query string in such like that index.php?name=tejaspatel
i have table in record is available below
select * from emp where name = :name // it is query string parameter
emp
name
tejas patel
then how to match this record ?
View 8 Replies
View Related
Oct 15, 2008
I have a file name field in my database that stores each file name with the extension .TXT and almost each file name is different.I would like to remove this extension from all of the file names without using the different file name each time I update. Is there any SQL statement that will allow me to do this? I am using Oracle.
View 1 Replies
View Related
Mar 12, 2011
how to Completely remove Oracle 11g from my computer ?
View 2 Replies
View Related
Sep 7, 2012
In V$BACKUP_PIECE there are many backup pieces OBSOLETED and EXPIRED with fields: handle set to null and deleted to as "YES"
I ran the CROSSCHECK BACKUP and DELETE OBSOLETE and DELETE EXPIRED BACKUP, and they works.
But how can I remove these lines? Are they still important for anything?
View 3 Replies
View Related
May 26, 2008
I install oracle 10 g but I don't remember password . so I then remove oracle 10 g by select Universal Installer and click Deinstall and then reinstall but I can't reinstall because I don't remove/delete oracle_home I then delete folder in path C:Oracle and reinstall Question
1.in this remove oracle 10 g method true or wrong if wrong , How effect on my computer
2.why after reinstall my computer is very slow.
View 5 Replies
View Related
Mar 24, 2011
remove duplicates from my collection(table type).Or an alternate solution for my issue is get clean data into my collection which i am not able to get to either.
Object creation
create table testingtype_table(ordernumber number,org_id number , company_name varchar2(10))
insert into testingtype_table values (1124,2424,'cbaaa');
insert into testingtype_table values (1124,2424,'cbaaa');
create or replace type testingtype_obj as object (ordernumber number,org_id number , company_name varchar2(10));
create or replace type testingtype_tab as table of testingtype_obj;
Code Block
declare
l_testingtype_tab testingtype_tab := testingtype_tab();
begin
select distinct testingtype_obj(ordernumber
,org_id
,company_name)
bulk collect into l_testingtype_tab
from testingtype_table;
end;
If only i can get a way to bulk collect only distinct values into the table type that will just do great but when i try the above (with distinct highlighted in red) it throws an error
ORA-22950: cannot ORDER objects without MAP or ORDER method
View 4 Replies
View Related
Oct 11, 2013
Removing duplicates from a string that contains years and "-".
Example: 1988-1997-2000-2013-1998-1965-1997-1899
I know this can be done in regular expressions but have no experience in this subject.
select REGEXP_REPLACE(.....) from dual;
View 16 Replies
View Related
Aug 3, 2011
i have two tables
Quote:
Table has following data.
id indicator
---------------
1 A
2 A
3 A
Quote:
Table2 has
id indicator
---------------
1 X
1 X
2 X
3 X
I would like to have following output ( Am running query on Toad)
t1_id t1_indicator t2_id t2_indicator
---------------------------------------------
1 A 1 X
1 X
2 A 2 X
3 A 3 X
View 4 Replies
View Related
Mar 22, 2013
I need to removed special characters (!, ", #, $, %, &, /, () from a string, i have a table with sll this special characters and words that i have to remove from the string.
How can i do that ?
i have a string with |R!$#&2-_D%2 and i want to get R2-D2
SELECT '|R!$#&2-_D%2' as Original, 'R2-D2' as Correct
FROM DUAL
View 5 Replies
View Related
May 28, 2012
how can i get distinct records through this coding, when i add select distinct col1, col2, col3 from tablename where RECD_ON between :control. REC_ FROM and :control.REC_TO; in 1st qry after begin, this form not retrive any data from database, then how i get distinct rows through this coding. is there any option in property plattee to get distinct rows.
declare
qry varchar2(5000);
n number;
alert number;
Begin
[code]....
View 8 Replies
View Related
Jun 10, 2013
I do have a query which gives me two rows of data.(This might be more than two in other cases)
The data I get is formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
European Patent EP 101 Mokilosu
What I want to get should be formed as :
COUNTRY_DESCRIPTION COUNTRY_CODE EVENT_NUMBER EVENT_DATE APPLICANT
European Patent EP 101 Kiksu
Mokilosu
As you can see, I removed the first four columns because the eventkey is the same. In this case, there is only the applicant which is different.So the rest should be blank.
The code I do use to get this data:
SELECT
TABLECOUNTRY.COUNTRYDESCRIPTION "COUNTRY_DESCRIPTION"
,TABLECOUNTRY.COUNTRYCODE "COUNTRY_CODE"
,OWNER.NAME "APPLICANT"
,CASEEVENT.EVENTNUMBER "EVENT_NUMBER"
,TO_CHAR(CASEEVENT.EVENTDATE,'DD.MM.YYYY') "EVENT_DATE"
[code]....
So what to modify in the query to get these columns of the second row blank?
View 17 Replies
View Related
Aug 15, 2010
How to write a pl/sql query to trim/remove more than one character from string.
Like the itemfield is 'Profit CY' I want it to show as 'Profit' but only for itemfields that say 'Profit CY' in the column for remaining items in column such as 'Loss CY' should stay as it is.
View 25 Replies
View Related
Nov 27, 2009
My query is suppose to capture the student's number, date of birth, and number of students which that students mentors. I am suppose to show only the 5 oldest students
SELECT s.std_num, s.birth_date, COUNT(s2.mentor_num)
From Student s left outer join Student s2
on s2.mentor_num=s.std_num
GROUP BY s.std_num, s.birth_date
ORDER BY s.birth_date ASC;
This function works but the problem is in displaying the five records because whenever I apply the rownum<=5 function the counter returns incorrect results. I tried writing another query to fix it but I could not dispay the counter anymore
SELECT ROWNUM as SENIOR, s.std_num, s.birth_date, COUNT(s2.mentor_num)
FROM (SELECT s1.std_num, s1.birth_date, COUNT
(e2.super_id)
From Student s left outer join Student s2
on s2.mentor_num=s1.std_num
GROUP BY s1.std_num, s1.birth_date
ORDER BY s1.birth_date ASC) S
WHERE row_num<=5;
This code only works if I remove the count in the first line but I need to display the number in my result.
View 2 Replies
View Related
May 16, 2007
In Oracle XE when I run PL/SQL in the SQL Commands window I will sometimes get a "Not found" error. It further reads, "The requested URL /apex/wwv_ flow.show was not found on this server". I found that if I remove all of the indentations from the lines of the PL/SQL code everything will work fine. Talk about a screwy problem. So, if you have that problem just take all of your PL/SQL code and slam it up against the left margin.
View 1 Replies
View Related
Nov 8, 2012
I want to remove duplicates from a column MAIN_TABLE based on TLEVEL Column:
create table UNIQ_TEMP
( TLEVEL NUMBER(10,0),
TABLE_NAME VARCHAR2(30),
MAIN_TABLE VARCHAR2(30)
);
[code]....
My Requirement is:
MAIN_TABLE= MARKETING_OPTIN
EXISTS IN MAX(TLEVEL)
REMOVE DUPLICATE MARKETING_OPTIN FROM OTHER LEVELS
This should apply to all the values in column MAIN_TABLE
View 12 Replies
View Related
Nov 21, 2006
I am using an Oracle view and package to extract data from my DB to build an XML file on the fly.
My problem is that on very rare occasions, an invalid XML character will be in the database. This, of course, causes my XML file to error.
My question is: What are the possible ways to remove invalid XML characters when selecting out of a DB field? Any function that has been written for this type of thing, or is a VERY long 'translate' more of what I should be doing?
View 1 Replies
View Related
Jul 11, 2011
I want to truncate table partition but I'm getting error:
CODEORA-02266: unique/primary keys in table referenced by enabled foreign keys
because of table has a nested table. Currently this column is not in use so I could drop it, but I want to avoid it (table is huge). Is there another posibility to do truncate partitions in this table? ALTER TABLE ... SET UNUSED doesn't resolve the problem.
View 2 Replies
View Related
Jan 26, 2011
I have following query:
type EMP_REC is record
(
id number,
name varchar2(20),
dept number
);
type EMP_TBL IS TABLE OF EMP_REC INDEX BY BINARY_INTIGER;
EMP_TABL1 EMP_TBL;
select * BULK COLLECT into EMP_TBL1
FROM emp;
How to remove duplicate records from EMP_TBL1 collection if exists.I don't want to remove duplicate records from main table. But actually want to remove duplicate records from EMP_TBL1 collection if exists.
View 1 Replies
View Related
Jul 9, 2013
RE: Partial install of SSO leaves orphaned instances.
Background: Lost connection to Linux system while attempting SSO install. During reinstall installer states instance name already in use - suggests using different name.
Using deconfig.pl tool (user: cn=orcladmin) to remove SSO configuration, but error encountered. Reason: incorrect SYS password. Note: Can access db using SYS password with no problem.
Question: Does deconfig.pl completely remove SSO configuration? If not, what other steps are required to completely remove SSO?
View 1 Replies
View Related
May 8, 2013
how to remove duplicate rows from table?
View 6 Replies
View Related
Apr 8, 2010
I have a sql like
select TRIM(column_name)
from user_tab_columns
where table_name = 'MTL_SYSTEM_ITEMS_VL' AND COLUMN_NAME IN ('DESCRIPTION ');
[Code]....
Like this i have many rows. What clause other than trim i should apply to no cosider the spaces?
View 27 Replies
View Related
May 12, 2011
I have a query like
SELECT country_name,
substr(SYS_CONNECT_BY_PATH(product_name,','),2) as PRODUCT_NAME,
substr(SYS_CONNECT_BY_PATH(SPEED_VALUE,','),2) as SPEED_VALUE,
substr(SYS_CONNECT_BY_PATH(i.SUPPLIERNAME_ACCESSPROTYPE,','),2) as SUPPLIERNAME_ACCESSPROTYPE
FROM (SELECT b.country_name,b.product_name,b.speed_value,(supplier_name|| supplier_product || access_product_type)as
[code].......
In the result , I am getting repeated values for product_name and speed value,something like 'ALL Products,All Products,All Products'in the product_name column and '128Kbps,128Kbps'in Speed_vale.i am not able to remove the repeated values here.
View 2 Replies
View Related
