SQL & PL/SQL :: Locate Occurrence Of Symbols Combination
Aug 5, 2011
My need is to locate an occurrence of symbols starting from "s." (non-capital letter), following by word (with any capital letter at the beginning) and ending with ", " (comma and space symbols).
Ex:
select 'jeklghje, s.Glkgje, u.slgjwek, 904869' as tt from dual union all
select 's.Tklgj, u.slgjwek, 23578, elslgjs' as tt from dual union all
select 's.klgj, u.ekgjes, 238573, dlsjkgj' as tt from dual
I'm looking for occurrence of "s.Glkgje, " and "s.Tklgj, ".
I think some combination of REGEXP_INSTR and REGEXP_SUBSTR should be useful, but I'm not familiar with these functions so good yet.
When I try to start the database or ASM instance its showing the following error.
SQL> connect sys as sysdba Enter password: Connected to an idle instance. SQL> startup ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+FLASH_RECOVERY_AREA/XXXXX/spfileXXXXX.ora' ORA-17503: ksfdopn:2 Failed to open file +FLASH_RECOVERY_AREA/XXXXX/spfileXXXXXX.ora ORA-15077: could not locate ASM instance serving a required diskgroup SQL>
when am trying to view the data's by below Sql its show name as null for FLASH_RECOVERY_AREA. so I couldn't even set compatible ATTRIBUTE
SQL> select NAME , COMPATIBILITY, DATABASE_COMPATIBILITY from V$ASM_DISKGROUP; NAME COMPATIBILITY DATABASE_COMPATIBILITY ------------- ---------------------- ----------------------------------- RUBIKON_DATA 10.1.0.0.0 10.1.0.0.0
SQL> show parameter asm_disk NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ asm_diskgroups string RUBIKON_DATA, FLASH_RECOVERY_A REA asm_diskstring string /dev/mapper/Rubikon_data1, /dev/mapper/Rubikon_data2, /dev/mapper/Rubikon_fb
because of name is null mount mode as CLOSED and HEADER_STATUS as INCOMPATIBLE.
SQL> select name, mount_status, header_status,mode_status,state, path from v$asm_disk ; NAME MOUNT_S HEADER_STATU MODE_ST STATE PATH -------------- ------- ------------ ------- -------- ------------------------- CLOSED INCOMPATIBLE ONLINE NORMAL /dev/mapper/Rubikon_fb RUBIKON_DATA_0000 CACHED MEMBER ONLINE NORMAL /dev/mapper/Rubikon_data1 RUBIKON_DATA_0001 CACHED MEMBER ONLINE NORMAL /dev/mapper/Rubikon_data2
I'm trying to find a way to see if a value occurs more than once in a string. I just need to know "T/F", or "Y/N", etc.
The string will be comma delimited.
String: '1,2,3,1' Ans: "T"
String: '1,2,3,4' Ans: "N"
They do need to match exactly. for instance
String: '1,2,3,1a' Ans: "N"
Using some code I found on this site, I coded this but I'm sure there's a better way. Is there??
--Check for Duplicate combination row values BEGIN --Query will split the string into individual pieces. --Group the pieces to see if any 2 rows are the same --If no rows are the same then "no_data_found" exception is thrown SELECT 'T' INTO vResult
I am using regexp_replace function to replace the string between the double quotes with the first occurance but i am able to replace first occurance but I am not getting remaining string.
For example:
select REGEXP_replace('Parent.addChildByName("DS-Id of OAL(BROBA)")||parent.add("DS-Id of OAL(BROBA)")','["]:print:+:punct::print:*["]','XXXX') from DUAL
O/P: Parent.addChildByName(XXXX)
Expected O/P : Parent.addChildByName(XXXX)||parent.add("DS-Id of OAL(BROBA)")
version : Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
i want to ,remove consecutive occurance from string
Example I/P: 'POWELL POWELL BRIAN K AND BONNIE POWELL JARRELL JARRELL' to O/P : 'POWELL BRIAN K AND BONNIE POWELL JARRELL'I tried the below code is Working fine , But i wanted to do this using Regexp or Some other Better Method WITH T
'm using the "Highlight Words" column formating for a report. There, I'm using the item syntax: &P1_RENVOI. Problem is, when the report appear, only the first occurence of the value of item "P1_RENVOI" is in red for every field in that column.
Is this a normal feature or should all occurences be put in red?
I need to search a specific pattern from a source code. In word, I need to check whether "getCode" has been called or not, for all the string inside double-quote("). Following are sample code lines -
Now, expression should be such that it will return true during check for 1, 3 and 4, although, for 3 & 4 getCode has been called for part of the String.
CREATE TABLE TMP_split AS SELECT 1000 AS grp_id, 'abc' AS ATTRIB, 60 AS PCT FROM DUAL UNION ALL SELECT 1000 AS grp_id, 'pqr' AS ATTRIB, 40 AS PCT FROM DUAL;
I need a "solution", guidance to a problem I have to solve. I have different letters with a value associate to it like
A:10, B:20, ..., G:250 and I have a Target to reach TARGET= 90
and I need to find among all letters I have which combination is equal or closest to my target.
A:10 B:20 C:20 D:20 E:30 F:40 G:250
IF there is more than one solution possible, the first found is perfect. With my example I can reach 90 with different combinations:
A+B+C+F A+B+D+F A+C+D+F B+C+D+E B+E+F C+E+F D+E+F
To complicate the things I have up to 10 different letters that need to combine to match my target. If there is no combination that exactly match my target, the closest higher combination is picked.
I have a requirement here. User wants when he pressed 'CTRL+E' on the form item, he must get another form. Actually 'CTRL+E' is defined for EDITOR. Then how could I achieve this?
Now the ultimate aim is to find unique combinations (even if the same combination is present in different rows, we have to consider it as one combination), along with its frequency, result will be like below:
BISCUIT [occurence in 2 transactions and total 4 transactions] = 2/4 = .5 BREAD [occurence in 2 transactions and total 4 transactions] = 2/4 = .5 COKE [occurence in 1 transactions and total 4 transactions] = 2/4 = .25 MILK = 3/4 = .75
[Code]....
writing query to find unique combination like above? What I have tried is as below:
create table test (row_no number, col_no number, item varchar2(50)) /
insert into test values (1,1,'BREAD'); insert into test values (1,2,'BISCUIT'); insert into test values (2,1,'BREAD');
[Code]....
But I am not able to form the exact query. let me know if this can't be done through a single query.
How to achieve "Prev_Value" column as shown below without using ORACLE analytic functions
I have records stored in table for various categories and based on ID / Name / Office / Product / Category combination I want to achieve previous value column through efficient SQL query
Col1 Col2 Col3 1 1 N 1 1 N 1 1 Y 2 1 N 2 1 N 2 1 N
I need in output only combination of 2 (col1 value) and 1 (col2 value). i.e. consider only those records where all records of key combination (col1, col2) have 'N'
How can I delete the duplicate combination of records from the below table.
CREATE TABLE test ( gidNUMBER(10), pidNUMBER(10) ); INSERT INTO test VALUES (10,20); INSERT INTO test VALUES (20,10); INSERT INTO test VALUES (25,46);
[code]....
The condition is if GID = PID and PID = GID then only one combination of these records should be retained. For example Out of 10-20 and 20-10 only one record should be retained.
i'm trying to import data to QUANTUM "oracle database" from Oracle database import assistant using OCDia.exe with SQL statement below and i'm getting this error message "Part Number and manufacturer combination must be unique"
------------------------------------------------------------------------------------------------------------------------------------- PROCEDURE ACE_LISTPRICE_LEADTIME (P_IMP NUMBER) IS C DIA_RL_PKG.CURSOR_TYPE ;
Given a table with some columns and data associated with that. Need to find out a column or a combination of some columns, so that the values or combination of values will be unique in the table.The table and number of columns and the columns will be dynamic.
I need to fire the KEY-DUPREC trigger as I click on the key combination Shift+P therefore; I added the following line of code in the frmweb.res file.
80 : 1 : "Shift+P" : 64 : "Duplicate Record"
It worked iff no error was raised. So, if I have a raise form trigger failure in the KEY-DUPREC trigger, a capital P will appear in the text field that called the trigger.
I have 3 tables in the Oracle database( emp, employee, emp1) which has following record values in it.
empidenamejob 7369, 'SMITH', 'CLERK'
I would like to list these 3 tables thru SQL/PLSQL, having the above record values combination. Also, the name of the columns could be different in all the tables i.e. name could be 'ename' in Emp table , and 'name' in Employee table. Is there way to do this in SQL or PLSQL ?
In my forms(version 9i), list item values showing with ALT + DOWN key combination but I want to change it to only DOWN key.I search this combination in FMRWEB.RES file but there isn't any entry of ALT + DOWN.
