PL/SQL :: Regular Expression Select

Jun 6, 2012

Sample Data:

SELECT 'A/BCD/CCE/DFFFF' test  from dual
Expected Output:

SELECT 'A' A,
'BCD' B,
'CCE' C,
'DFFFF' D
FROM DUAL;  

View 6 Replies


ADVERTISEMENT

SQL & PL/SQL :: Regular Expression In Oracle 10g

Apr 13, 2013

What is the use of Regular Expression?

View 1 Replies View Related

SQL & PL/SQL :: How To Use Substr Or Regular Expression

Jan 21, 2011

I 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 ?

View 11 Replies View Related

PL/SQL :: Regular Expression Matching All Given Words

Nov 8, 2012

I'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.

View 7 Replies View Related

SQL & PL/SQL :: Remove Special Chars Using Regular Expression

Apr 9, 2013

Can 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.

View 4 Replies View Related

SQL & PL/SQL :: Finding Date Value In Given String By Using Regular Expression

Jul 3, 2012

I 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].......

View 3 Replies View Related

SQL & PL/SQL :: Use Of Regular Expression In Real Time Scenario

Jul 24, 2012

Regular Expression in oracle??Especially the use of this in real time scenario.

View 4 Replies View Related

PL/SQL :: How To Perform A Pattern Check - Regular Expression

Oct 16, 2012

Is 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;

View 6 Replies View Related

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.

View 2 Replies View Related

SQL & PL/SQL :: Regular Expression To Find Commits In All Oracle Source?

Oct 14, 2013

I 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'

View 18 Replies View Related

PL/SQL :: Regular Expression To Check Number With At Least One Decimal Point?

Nov 18, 2013

I 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

View 3 Replies View Related

JDeveloper, Java & XML :: Regular Expression For Exact Word And Occurrence

Mar 15, 2012

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 -

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.

View 2 Replies View Related

JDeveloper, Java & XML :: Extract Data From Clob Field Using Regular Expression

Oct 6, 2011

I 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.

View 1 Replies View Related

SQL & PL/SQL :: Execute The Expression In Select Statement

Feb 12, 2013

CREATE 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;

View 15 Replies View Related

Reports & Discoverer :: ORDER BY Item Must Be Number Of A SELECT-list Expression

Sep 18, 2009

When 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

View 3 Replies View Related

SQL & PL/SQL :: Expression Must Have Same Datatype As Corresponding Expression

Dec 22, 2010

ved>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?

View 9 Replies View Related

Using PL/SQL Table With Regular Query

Apr 22, 2009

How 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.

View 2 Replies View Related

PL/SQL :: The Regular Expressions And GUID

Jul 16, 2012

I 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 Related

SQL & PL/SQL :: Copy Data At Regular Interval

Mar 2, 2011

I 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 Related

Perform Regular Updates On Several Oracle Tables

Sep 26, 2012

I 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.

View 9 Replies View Related

Logical Backup Of All Application Tables At Regular Intervals

May 18, 2010

I want to maintain the logical backup of all the application tables At regular intervals. Which option would i use?

View 1 Replies View Related

SQL & PL/SQL :: Extract Email ID From A String / Text Using Oracle Regular Expressions

Oct 14, 2013

code 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

View 9 Replies View Related

SQL & PL/SQL :: How To Load Flat File Into Database At Regular Interval Time

Apr 5, 2011

how can we load a Flat file into a Database At Regular Interval Time.

View 2 Replies View Related

Application Express :: Unable To Size Pop Window That Redirects To Regular URL Using Dynamic

Nov 8, 2013

I 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?.

View 1 Replies View Related

Data Guard :: Take RMAN Full Backup Of Standby Database On Regular Basis?

Jun 8, 2012

I 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.

View 2 Replies View Related

Automatic Storage Management :: Place Voting Files In Quorum Or Regular Failgroup?

Oct 29, 2013

About 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?

View 2 Replies View Related

Real Application Clusters :: Monitor Incident Or Regular Health Checkup Under Grid And Oracle User?

Apr 15, 2013

I 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 Related

PL/SQL :: How To Format A Value Using Reg Expression

Jan 11, 2013

I need to format a value using Regular Expression.

9911223344, 9911223344
9911223344
11223344Result
(99) 1122-3344, (99) 1122-3344
(99) 1122-3344
1122-3344

View 4 Replies View Related

PL/SQL :: Not A Group By Expression

Apr 1, 2013

when 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]....

View 19 Replies View Related

SQL & PL/SQL :: Objects Containing Expression Cannot Be Used As Assignment

Apr 4, 2012

I'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()?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved