PL/SQL :: Regular Expression Select
Jun 6, 2012Sample Data:
SELECT 'A/BCD/CCE/DFFFF' test from dual
Expected Output:
SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;
ADVERTISEMENT
SQL & PL/SQL :: Regular Expression In Oracle 10g
Apr 13, 2013What is the use of Regular Expression?
View 1 Replies View RelatedSQL & PL/SQL :: How To Use Substr Or Regular Expression
Jan 21, 2011I have a following table,
CREATE TABLE checkdata
( col1 VARCHAR2(2000)
);
INSERT
INTO checkdata VALUES
[code]......
I need output as ,
INT8144925446-20110118T123723Z
INT8144925448-20110118T123730Z
How do i use Substr and Regular expression here ?
PL/SQL :: Regular Expression Matching All Given Words
Nov 8, 2012I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.
select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');
Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.
search words : one two
one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.
SQL & PL/SQL :: Remove Special Chars Using Regular Expression
Apr 9, 2013Can we use regular expression to eliminate all characters other than -
A to Z
0 to 9
Special characters like - &,%,~,@,#,$,^,*,_,+,=,,/,<,>
Example
String - TEST@#_~````}{!!!12311HELLO
Expected result - TEST@#_~12311HELLO
how we can use regular expression to achieve this result.
SQL & PL/SQL :: Finding Date Value In Given String By Using Regular Expression
Jul 3, 2012I have a Identifier column with start and stop dates along with description .
Two dates are separated by '-'. But the position of that character(-) is not constant always. Depending on the instr function I am able to divide the start and stop dates. But I am getting the performance problem because of huge data
I think the same logic will be implemented by regular expression also . How to write the equivalent logic by using regular expressions
create table REG_EXP_TEST
(
TRANS_ID NUMBER(10),
TRANS_IDENTIFIER VARCHAR2(250)
) ;
insert into REG_EXP_TEST
(TRANS_ID, TRANS_IDENTIFIER)
values
[code].......
SQL & PL/SQL :: Use Of Regular Expression In Real Time Scenario
Jul 24, 2012Regular Expression in oracle??Especially the use of this in real time scenario.
View 4 Replies View RelatedPL/SQL :: How To Perform A Pattern Check - Regular Expression
Oct 16, 2012Is there a way to perform a pattern check on a value ?
For example: 654321HD9
The pattern is 6 numbers, followed by 2 letters, followed by 1 number. The data type for the attribute is a string.
Examples of right or wrong
654321HD9 - correct value
654321HD - wrong value
654321111 - wrong value
HD1111111 - wong value
The pattern has to be as i mentioned above (6 number, 2 letters, 1 number) otherwise its wrong. My pattern does not cover all cases.
select REGEXP_SUBSTR('654321HD9', '[0-9]+[A-Za-z]+[0-9]+') from dual;
PL/SQL :: Regular Expression To Remove Space In HTML Tag?
Nov 6, 2013, My HTML string is like below. select '<CityName>RICHMOND</CityName> <StateCd>ABCD CDE <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd>' Str from dual Desired Output is<CityName>RICHMOND</CityName><StateCd>ABCD CDE <StateCd/><CtryCd>CAN</CtryCd><CtrySubDivCd>BC</CtrySubDivCd> i.e.
want to remove those spaces from tag value area having only spaces otherwise leave as it is.implement the same using Regular expression.
SQL & PL/SQL :: Regular Expression To Find Commits In All Oracle Source?
Oct 14, 2013I would like to write a select that would return all places in DB that are commiting transaction.
E.g. package for testing:
CREATE OR REPLACE PACKAGE test.TEST_COMMIT AS
PROCEDURE THE_ONLY_COMMIT_IN_DB ;
END TEST_COMMIT;--not a match
CREATE OR REPLACE PACKAGE BODY test.TEST_COMMIT AS
[code]....
The select should return 4 rows with --ok.
SELECT *
FROM ALL_SOURCE ASO
WHERE
REGEXP_LIKE(ASO.TEXT,'commit(s*);','i' )
AND name = 'TEST_COMMIT'
PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?
Nov 18, 2013I would like to use the REGEX_LIKE to check a number with up to two digits and at least one decimal point: Ex.10.11.1112 This is what I have so far.
if regexp_like(v_expr, '^(d{0,2})+(.[0-9]{1})?$') t
JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence
Mar 15, 2012I 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 -
1.->if(val==23){ month_desc = "a sample data"; }
2.->if(val==23){ month_desc = getCode("a sample data"); }
3.->if(val==23){ month_desc = "a " + getCode("sample data"); }
4.->if(val==23){ month_desc = getCode("sample data"); var2="sample data2";}
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.
JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression
Oct 6, 2011I want to extract the data from the Clob field. I have the following table,create table test123(col1 char(24), col2 clob); And following data,
Insert into test123 (col1,col2) values ('ABCDE','<?xml version="1.0" encoding="UTF-8"?>
<Attributes>
<Attribute DataType="Text-40" DisplayName="DropDirectory"
IsNotDeletable="Y" Modifiable="Y" Name="DropDirectory" Value="${Dir1}"/>
<Attribute DataType="Text-40" DisplayName="PrinterAlias"
IsNotDeletable="Y" Modifiable="Y" Name="PrinterAlias" Value="\Printer3Printer4"/>
<Attribute DataType="Text-40" DisplayName="PrintServerHostName"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerHostName" Value=""/>
<Attribute DataType="Count" DisplayName="PrintServerPort"
IsNotDeletable="Y" Modifiable="Y" Name="PrintServerPort" Value="2723"/>
</Attributes>');
[code]....
The data will be available like this only, The same XML tag is used everywhere.
SQL & PL/SQL :: Execute The Expression In Select Statement
Feb 12, 2013CREATE TABLE TEST1
(
OFFICE_PRODUCTS NUMBER,
OFFICE_ELECTRONICS NUMBER
)
Insert into TEST1 (OFFICE_PRODUCTS, OFFICE_ELECTRONICS) Values(1, 0);
COMMIT;
CREATE TABLE TEST2
(
EXPORT_FIELD_NAME VARCHAR2(100 BYTE),
EXPORT_COLUMN_EXPRESSION VARCHAR2(100 BYTE)
)
Insert into TEST2
(EXPORT_FIELD_NAME, EXPORT_COLUMN_EXPRESSION)
Values ('A1', 'least(OFFICE_PRODUCTS, OFFICE_ELECTRONICS)');
COMMIT;
I want to be execute the expression should run in select statement how to do? and tried as like below,it's not working.
select (select EXPORT_COLUMN_EXPRESSION from test2 where EXPORT_FIELD_NAME='A1') FROM TEST1;
Reports & Discoverer :: ORDER BY Item Must Be Number Of A SELECT-list Expression
Sep 18, 2009When i'm sorting on a calculated field i'm getting the following error:
ORDER BY item must be the number of a SELECT-list expression
SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression
Dec 22, 2010ved>create table test900 ( a number, b number);
Table created.
ved>insert into test900 values( 9,'');
1 row created.
ved>insert into test900 values( 10,null );
1 row created.
ved>select * from test900;
A B
---------- ----------
9
10
ved>select nvl(length(b),0) from test900;
[code]....
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression..Why the above sql ( case 2 ) gives error?
Using PL/SQL Table With Regular Query
Apr 22, 2009How to use a table type variable with a regular query? For example, in my PL/SQL proc I have a table of employee names.
v_emp_tbl; --contains 'John','Sally','Ted'
Then I want to get names in a table called tbl_employees which are not in the pl/sql table. So if the table contains 'John','Sally','Ted',and 'Don' then I want to see 'Don'.
Not sure how to do this. Essentially I want something like:
Select emp_name into v_single_emp
from tbl_employees where emp_name NOT IN v_emp_tbl;
I know I can loop through the pl/sql table but I wanted to see if there is some other way.
PL/SQL :: The Regular Expressions And GUID
Jul 16, 2012I am needing to select GUIDS from a table and for this, I need the Regular Expression. My Perl is not good and not good Regular Expression. My database is Oracle 11.2.0.2.0 and the Operating System of the Machine is Linux (Oracle Version 6).
View 4 Replies View RelatedSQL & PL/SQL :: Copy Data At Regular Interval
Mar 2, 2011I have this remote database A and database B. DB A has 10 views and DB B has 10 tables. I have to pull out data from views of DB A and load into tables of DB B at regular intervals. How do I do this job?
View 3 Replies View RelatedPerform Regular Updates On Several Oracle Tables
Sep 26, 2012I am attempting to perform regular updates on several Oracle tables. The scripts performing the updates are scheduled to run every two minutes, get a value and update the table with that value.
The value doesn't always change but the scripts will still attempt to perform an update.
The same script is part of 7 objects, all of them are scheduled to run at the same time. They update the same table but never the same row.Even though the script is mostly the same on the 7 objects, they run completely independently of each other. The first object will usually perform the update without any problems but when it comes to the second object the script will time out.
Logical Backup Of All Application Tables At Regular Intervals
May 18, 2010I want to maintain the logical backup of all the application tables At regular intervals. Which option would i use?
View 1 Replies View RelatedSQL & PL/SQL :: Extract Email ID From A String / Text Using Oracle Regular Expressions
Oct 14, 2013code to extract email id (full email id) from a string/text using Oracle Regular Expressions.
Example -
"This is my String with email abc.efg@hij.com to test" O/p - abc.efg@hij.com
SQL & PL/SQL :: How To Load Flat File Into Database At Regular Interval Time
Apr 5, 2011how can we load a Flat file into a Database At Regular Interval Time.
View 2 Replies View RelatedApplication Express :: Unable To Size Pop Window That Redirects To Regular URL Using Dynamic
Nov 8, 2013I have an instruction like this in a pluggin: begin ...
htp.p('<font size="3" color="FireBrick">
Cannot find? Search on </font>' || '<
[URL]....
<font size="4" color="FireBrick"><b>Google</b></a>'); ...
end;
It displys the Google page in another window. How can size the wiindow? For example adding width=200, height=100 to the href tag doesn't work.... Do I need to use JavaSvript?If yes, any example?.
Data Guard :: Take RMAN Full Backup Of Standby Database On Regular Basis?
Jun 8, 2012I use windows 2003 server, oracle 10.2.0.4.0.My question is related to maintaing a standby database
1. Do we need to take RMAN full backup of standby database on a regular basis? At the moment I dont take backups of standby database.
2. I have FRA on standby database. The archivelogs from primary go to FRA on standby. I delete my archivelogs as follows:
DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-3';
Though the archivelogs are delete there are still empty folders of archivelogs that are not deleted. How to get rid of them automatically.
Automatic Storage Management :: Place Voting Files In Quorum Or Regular Failgroup?
Oct 29, 2013About the concept of quorum failgroup ORACLE says:QUORUM disks, or disks in quorum failure groups, cannot contain any database files,the Oracle Cluster Registry (OCR), or dynamic volumes. However, QUORUM diskscan contain the voting file for Cluster Synchronization Services (CSS). Oracle ASMuses quorum disks or disks in quorum failure groups for voting files wheneverpossible.
To sum up, i think the different between a regular failgroup and a quorum failgroup is a quorum failgroup can only contain vote files and a regular one can contains multiple kinds of files.So i didn't see any advantage to place voting files on a quorum failgroup than on a regular one. Why Oracle introduce the concept of quorum failgroup?
Real Application Clusters :: Monitor Incident Or Regular Health Checkup Under Grid And Oracle User?
Apr 15, 2013I am new in RAC scenario though i have some system to look after. I want to know, like single instance there is alert log,trace log where we should investigate to check if there is any problem. For RAC system which are the logs that i can find the monitor the incident or regular health checkup under grid and oracle user?
View 3 Replies View RelatedPL/SQL :: How To Format A Value Using Reg Expression
Jan 11, 2013I need to format a value using Regular Expression.
9911223344, 9911223344
9911223344
11223344Result
(99) 1122-3344, (99) 1122-3344
(99) 1122-3344
1122-3344
PL/SQL :: Not A Group By Expression
Apr 1, 2013when i am running the below plsql block i am getting the error like not a group by expressiong.
DECLARE
CURSOR Cur_st
IS
SELECT DISTINCT
CAST (A.STO_NO AS VARCHAR2 (5 CHAR)) AS BU_CODE,
CAST ('STO' AS VARCHAR2 (3 CHAR)) AS BU_TYPE,
CAST (NULL AS VARCHAR2 (7 BYTE)) AS CUST_NO,
CAST (A.CUR_CODE AS VARCHAR2 (3 BYTE)) AS CUR_CODE,
TO_DATE (A.SALES_DATE, 'YYMMDD'),
CAST (A.RECEIPT_NO AS VARCHAR2 (10 BYTE)),
[code]....
SQL & PL/SQL :: Objects Containing Expression Cannot Be Used As Assignment
Apr 4, 2012I'm working with Object types containing other object types, and I'm getting the error PLS-00363 (Expression cannot be used as an assignment).I'm putting exlpicity all 'SELF' parameters as 'IN OUT', but still get the error...
CREATE OR REPLACE TYPE TYP_PERSON AS OBJECT (
strName VARCHAR2(100),
--
CONSTRUCTOR FUNCTION TYP_PERSON RETURN SELF AS RESULT,
--
MEMBER FUNCTION getName (SELF IN OUT TYP_PERSON) RETURN VARCHAR2,
MEMBER PROCEDURE setName (SELF IN OUT TYP_PERSON, pNewName VARCHAR2)
) NOT FINAL;
[code]....
How can I do this parentObject.getChildObject().setChildFunction()?