SQL & PL/SQL :: Using Regexp_replace To Eliminate Duplicate String With Delimiter
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
ADVERTISEMENT
Sep 1, 2010
I have table with below values
SELECT * FROM dup_val
1
1
1
1
2
2
2
2
3
The result set should be like
1-1
1-2
1-3
1-4
2-1
2-1
2-2
2-3
3-1
the query get the above result set.
View 10 Replies
View Related
Jan 27, 2009
I have to eliminate duplicate pairs from the following data set.
for ex. Adney Vaughan and Berkly Wassen appears in both AG1 and AG2. how can i get rid of these repititive rows?
AG1 ----------- AG2
Acton Wibert ---- Currier Barhydt
Adney Vaughan --- Luella Edmund
Adney Vaughan --- Berkly Wassen
Alden Anstruther --- Courtney Gavet
Ashley Alvord --- Saunders Buel
Aswin Wilbraham --- Dale Cooper
Barnum Sears --- Grayson Lightfoot
Berkly Wassen --- Luella Edmund
Berkly Wassen --- Adney Vaughan
Bersh Musgrave --- Derward Knight
Berthilda Darrell --- Broderick Reynold
Broderick Reynold --- Berthilda Darrell
View 1 Replies
View Related
Nov 24, 2011
I need to delete the duplicate values from plsql table OR move the distinct values in plsql table to other plsql table.
how can i do this ?
DECLARE
TYPE alist IS TABLE OF VARCHAR2(10) INDEX BY BINARY_INTEGER;
p_tbl alist;
BEGIN
p_tbl(1) := 'A1';
p_tbl(2) := 'B2J';
p_tbl(3) := 'A1';
[code]......
The p_tb1 table contains all the above values including duplicates. Now I need only distinct values to be copied in another plsql table of same type.
View 14 Replies
View Related
Feb 19, 2008
I was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com
I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.
View 1 Replies
View Related
Apr 29, 2011
In my table i have a field called swistmsg, which contains value as
:201:0001OTT11000004|:23b1:CRED|:32a1:01-01-2011|:32a2:USD|:
32a3:1000.|:33b1:USD(similarly around 100 rows)
and my requirement is
201 0001OTT11000004 23b1 CRED 32a1 01-01-2011 32a2 USD 32a3 1000 33b1 USD
and i have eliminate '.',':'&'|' (only quoted chars)from the string separate string
View 17 Replies
View Related
Dec 4, 2012
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).
View 4 Replies
View Related
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
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
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 2, 2012
I am trying to write a code to identify the delimiter in the file ( which is in the form of table(id, raw) in system)then I take this delimiter and pass it as a parameter to SP which perform cleaning of this file(table) and creates another clean table.
The problem I am facing is until now the file was coming with one fixed (TAB) delimiter, but now it has come with different (SPACE), now here I want to develop the code to identify the delimiter place it in a variable an pass this as parameter to cleaning SP.
--here i want to develop code to identify delimiter from hosts_equiv file which has data as below
select * from hosts_equiv where left(raw,1) not in ('#','*') and isnull(raw,'')<>''
Raw id
---------------- ---
hiper USER1 1
hiper2 USER1 2
APX user2 3
Need to identify delimiter between e.g. hiper USER1 and pass it as a parameter to the raw_parse sp
declare Tab varchar(10)
set Tab = char(9)
exec raw_Parse
'hosts_equiv', -- From table ( entire file content is stored as table with Id record no sequence generated)
'CleanedHosts_Equiv', -- To table name, when passed it will clean the from table and places the cleaned data in to_table
[code]....
View 3 Replies
View Related
Dec 22, 2011
I am using sqlloader for loading the data into database by using csv file.My csv file is delimited by comma in that i am having a column which is having the , and line feeds targeted to load into a long data type.for example as below
descri,dfdfdfd,dfdfdf,
sdfsdf,
dfsdfd,
i want to move this column data into a single table column.But due to because of delimited "," it is splitting into number of columns
View 6 Replies
View Related
Jun 29, 2010
Need a query to find the count of presence of delimiter "," in column VAL for below mentioned table.
Table:
CREATE TABLE N_B_S_1
(
NUM NUMBER,
VAL VARCHAR2(20)
)
Data:
INSERT INTO N_B_S_1 VALUES(1,'A,B,C');
INSERT INTO N_B_S_1 VALUES(2,'D');
INSERT INTO N_B_S_1 VALUES(3,'ER,NF,G,H,XK');
INSERT INTO N_B_S_1 VALUES(4,'LQW,MBV');
INSERT INTO N_B_S_1 VALUES(5,'KS,YJ,WE,RQ,PM');
COMMIT;
Required output:
num, count of delimiters
===== ===========
1 2
2 1
3 4
4 2
5 4
View 5 Replies
View Related
Dec 15, 2010
I need to produce Excel spreadsheet from report builder.currently I am getting a format like,
ProductID;Count;CountUnique;
1001;12;16;
1002;11;6;
1004;16;666;
1006;60;86;
This format when opened in Excel does not group in separate columns resulting in all getting mixed up.I am looking a format like,
ProductID,Count,CountUnique,
1001,12,16,
1002,11,6,
1004,16,666,
1006,60,86,
This opens up clearly on excel. items like Product,Count are shown on separate columns along with the value.I am still looking into delimited formatting.
View 2 Replies
View Related
Sep 16, 2006
I am developing a TAB delimited oracle reports(6i).In which I am doing the following
1. One query for both text and excel reports
2. two layouts, one for standard another for excel(I have parameter to select whihc format user want to see)
3. In excel layout I putting one fielde for each row. And assigning each row with a formul;a column
4. In formula column I am sending data for that row with tab delimited as below
return(:Name || chr(9) || :Age || chr(9) || :depat) ;
5. I have registered in Apps
6. When I run the report in apps the output comes with tab space for each column
7. But I save the same file and try to open in excel with delimiter as 'TAB' its not spreading across cell. It is just coming in a single cell
View 4 Replies
View Related
Jan 4, 2012
I am trying to import data from below content.
Flat File
PARENTCHILDALIAS
PLAN_PCOTDefaultPlanning Customer
1001_BTPCOTDefaultGeneral Planning Customer
2000_BTPCOTDefaultNational Account Planning Customer
3000_BTPCOTDefaultDistributor Planning Customer
3010_BTPCOTDefaultEducation Planning Customer
3020_BTPCOTDefaultResearch Planning Customer
OPT1_PCOTDefaultOption 1 Planning customer
OPT2_PCOTDefaultOption 2 Planning customer
OPT3_PCOTDefaultOption 3 Planning customer
The problem here is , When you try to import to a table which has same columns . I skipped the first line when loading . The issue here is the second field is getting split in to the two columns . for e.g. :- Default goes to Child and Remaining goes to the Alias.
infarct there is a tab at the end of the each line. How to set the Sql loader settings correctly so that I can populated the end column in CHILD column only.!!!!
OPTIONS ( SKIP=1)
LOAD DATA
INFILE 'FlatFile.txt'
BADFILE ''FlatFile.bad'
DISCARDFILE ''FlatFile.dsc'
INTO TABLE "table"
FIELDS TERMINATED BY X'9'
OPTIONALLY ENCLOSED BY "''" TRAILING NULLCOLS
(PARENT,
CHILD,
ALIAS CONSTANT '')
View 5 Replies
View Related
Jun 11, 2013
I have a table like below:
tableA
aid des
1 concrete
2 wood
3 straw
4 plastic
5 fiber glass
6 other
tableB
bid material
01 1
02 2
03 3
01 2
01 2
02 3
01 5
The result I need when updating another table with this info is:TableC
edw_id bid Requirement
021 1 concrete, wood, fiber glass
032
025
123
521
I do not want :
concrete, concrete, concrete, wood, wood, fiber glass
SO far I am using the following but since I am dealing with hundreds of column that has the same material, when using listagg() from oracle 11.2g, they column width is too wide to fit into the required column.
update eris_data_work e set E.flex37 =
(select
LISTAGG(CM.des, ',') WITHIN GROUP (ORDER BY CM.des) AS casing_material
from CODE_CASING_MATERIAL CM, TBLCASING CA
where CM.code=CA.MATERIAL and CA.well_id=E.owner_oid AND CM.DES IS NOT NULL
GROUP BY CA.well_id)
where E.source='WWIS_ON'
I have even used the regexp_count() to try to eliminate duplicates however I have had no success so far
View 4 Replies
View Related
Mar 17, 2009
I am writing my first procedure and need to exclude two codes from a list of receipts. I can probaby use the following
and rc_receipt_code in (1, 2, 3, 4, 7, 8)
however can I use <> to eliminate 2 codes for instance can I say
select NVL(sum(rc_amt), 0)
into tot_cont
from trefrc
where rc_filer_seq = filer
and rc_receipt_code <> (5, 6);
View 1 Replies
View Related
Nov 2, 2011
i can't eliminate the spaces between values, i tried to use rtrim but still failed.
Set pagesize 0
set linesize 1000
set heading off
set feedback off
set colsep '|'
SELECT '200', '20002977', T0.TP, T0.Description, T2.FirstName, T2.LastName, 'Geography Code', SUBSTR(T3.aoManager, -6,5)
[code]....
View 6 Replies
View Related
May 10, 2012
I have a table with Date Field . While selecting the records its display like below format.
TO_CHAR(CHAR_DATE,'DD-MM-RRRRHH:MI:SSAM')
10-05-2012 12:00:00 AM
10-05-2012 03:26:16 PM
1 row doesnt have time, but in default it shows 12:00:00 AM, how to eliminate it. Display should be
10-05-2012
10-05-2012 03:26:16 PM
create table time_test (char_date date);
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012', 'MM/DD/YYYY HH:MI:SS AM'));
INSERT INTO TIME_TEST ( CHAR_DATE ) VALUES (
TO_Date( '05/10/2012 03:26:16 PM', 'MM/DD/YYYY HH:MI:SS AM'));
COMMIT;
select TO_char(CHAR_DATE,'dd-mm-rrrr HH:MI:SS AM') from time_test;
i need in to_char only, im using it in reports
View 4 Replies
View Related
Mar 5, 2010
I need to eliminate the blank spaces based on below conditions Consider name column with a value as
Input : "sa c h in Te nd ulka r" where "Sachin" is first name and "Tendulkar" is last name. there is more than 1 space between sachin and tendulkar (here its not displaying properly)
Condition :Second name is seperated from first name with more than 1 spaces and others are with 1 black space. I need to get result as Output:"sachin Tendulkar" ( there should be 1 blank space between first and last name in result.)
View 3 Replies
View Related
Jul 10, 2012
My requirement is as follows .
Oracle version details
BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
[code]....
View 6 Replies
View Related
Aug 21, 2013
How to eliminate duplicates from record types?Below code errors out with "Wrong number of arguments in call to MULTISET...."
error. DeclareTYPE ln_x_tab IS RECORD(x1 number ,x2 VARCHAR2(4000) ,x3 VARCHAR2(4000) ,x4 VARCHAR2(4000) ,x5 VARCHAR2(4000)); TYPE ln_x_type IS TABLE OF ln_x_tab INDEX BY BINARY_INTEGER; ln_x1 ln_x_type; ln_dist_x1 ln_x_type; gc_stmt varchar2(4000); Begin gc_stmt := ' SELECT x1, x2, x3, x4, x5 FROM table WHERE dynamic_conditions; EXECUTE IMMEDIATE gc_stmt BULK COLLECT INTO ln_x1; ln_dist_x1:= ln_x1 MULTISET UNION DISTINCT ln_x1; End;
I need ln_dist_x1 to have distinct records from table.
View 12 Replies
View Related
Feb 18, 2013
I have oracle database server set with Windows NT authentication. How can I get rid of this kind of authentication as this is holding up additional Windows Domain with its own PDS and so on. Or is it possible to move Oracle Database server to a different Domain and authentication to be coming from new domain?
View 4 Replies
View Related
May 9, 2012
i need to write a function to eliminate SUNDAY AND SATURDAY;
My criteria is
if My date as (5/19/2012 ) and i want to add 10 days to it themn my function should return 06/01/2012
if My date as (5/13/2012 ) and i want to add 12 days to it themn my function should return 05/29/2012
View 11 Replies
View Related
Apr 27, 2012
Below is an overs implication of what I need to extract from a donor list and am having some difficulty pulling the correct targets. I need to pull a donor who has had only one topic in the past year.
I only want id number 100 and not 120 since 120 has three topics in the past year, I tried using not exists...etc and can't seem to get the donors with the one topic 'HC' that is being requested.
example:
create table Topic(Idnumber number(8),topic varchar2(4));
INSERT INTO Topic(idnumber,topic)
VALUES (100, 'HC')
/
INSERT INTO Topic(idnumber,topic)
VALUES (120, 'HC')
/
[code].......
IDNUMBER TOPI
---------- ----
100 HC
120 IRS
120 PRS
120 HC
SQL> select idnumber from topic where topic in('HC');
IDNUMBER
----------
100
120
SQL> select idnumber from topic where topic in ('HC') and topic not in('IRS','PRS');
IDNUMBER
----------
100
120
View 12 Replies
View Related
Apr 28, 2010
I am spooling to a text file some output for a client. The file has 4 queries in it, one creates a header row, another a comment row, another the data rows and finally a trailer.
Code looks something like this:
/*
Custom Extract
Project: Plan Data Extract
Product: EOWin 4.02 - Oracle db
Use: Script to create above extract and spool results to text file
Input Parameters: &1 Path and name of output file
*/
[code]....
and the output looks like this:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
but the client and I want the output to look like this with no blank lines in between the queries:
HDR,04272010,Plan Data
CMT,Plan Num,Plan ID,Plan Name,Shares Allocated
DAT,1,01,Plan 01,99999999
DAT,2,02,Plan 02,99999999
DAT,3,03,Plan 03,99999999
DAT,4,04,Plan 04,99999999
FTR,4
View 3 Replies
View Related
Jun 19, 2012
I have this error (and solution):
ORA-02085: database link string connects to string
Cause: a database link connected to a database with a different name. The connection is rejected.
Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?
View 7 Replies
View Related
