SQL & PL/SQL :: Query To Eliminate Multiple Spaces?
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.)
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]....
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 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.
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 created a report (its basically a statement) without a header. When I run the report, blank lines appear at the top of the report before the details start printing. How do I get the report to print from the top of the page without leaving any blank lines.
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'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 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 have migrated from postgres to oracle entire databases however some tables have spaces how can they be removed and how can i found out all the tables in a schema with spaces?
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 */
And I just need to display only the T's that would meet the condition Col2 in (15,18) and Col3 in (20,19).
I try with a condition where col2 in (15,18) and col3 in (20,19) and that works, but I don't need to display T1, because T1 has a Col2=5 and Col3=20, I just have to display T2 that just satisfies all my conditions, Col2 in (15,18) and Col3 in (20,19). It's something like a vertical search.
I also have a query that queries a SQL Server database:
Select Agent, SUM([acd calls which have rung the agent])As CallsRung FROM Dashboard_stats Where Date = DATEADD(DAY,DATEDIFF(DAY,'20000101',GETDATE())-1,'20000101') Group by Agent
ID Status description Tracking ID 1 Strat Frog 1 2 Start Dog 2 3 Process Frog 1 4 Completed Dog 2 5 Start Rabbit 3 6 Error Frog 1 7 Stop Rabbit 3 8 Start Elephant 4 9 process Elephant 4 10 Start Human 5 11 Stop Human 5 12 Start Butterfly 6 13 completed Butterfly 6 14 start lion 7 15 error lion 8 16 complted lion 8 17 start tiger 9 18 error tiger 9
select * from Table-Name where datetime < to_date('2012/12/06:06:00:00', 'yyyy/mm/dd:hh24:mi:ss') And datetime > to_date('2012/12/04:22:00:00', 'yyyy/mm/dd:hh24:mi:ss')And not description in (Select * from Table-Name where Status like ('%Complete%' or Status like '%stop%') and description in (Select description from Table-Name where Status Like '%start%'));
Result should be " Frog and Elephant and tiger"
Start of every record(descrpition --status is Start) End of every record ( status is stop or done or completed) status process is in btwn (their will be mulitple records with name s//y to process...ie. process 1 ...process 2...process 3 )
I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.
SQL> select * from 2 (select deptno,job,sal 3 --,comm 4 from emp) 5 pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER')) 6 order by 1;
[code]....
I've tried this one also, but it didn't seems to be working.
SQL> select * from 2 (select deptno,job,sal,comm 3 from emp) 4 pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER')) 5 order by 1;
