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
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.
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:
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).
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
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?
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!
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;
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
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
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;
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.
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
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.!!!!
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
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]....
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.)
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
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.
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?
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
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');
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 */
