SQL & PL/SQL :: Regexp_replace Function

Jul 14, 2010

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

myString := regexp_replace(myString, '{(Date + [^[::digit:]]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

in hopes of making it ignore the numeric part but it, instead, treats occurrences as a non match. Alternatively, the call below

myString := regexp_replace(myString, '{(Date + [[:digit:]{1,})}', to_char(SYSDATE, 'FMMONTH DD, YYYY'));

matches correctly but replaces the numeric portion as well, so I'm left with just today's date instead of the calculated future date...

View 14 Replies


ADVERTISEMENT

PL/SQL :: Using Regexp_replace In Query

May 23, 2013

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?

View 5 Replies View Related

PL/SQL :: How To Use REGEXP_REPLACE To Remove ORACODES

Dec 27, 2012

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!

View 6 Replies View Related

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

STR REPLACED
------------------------------ ------------------------------
a#~#b#~#a#~#d a#~#b#~#a#~#d
a#~#b#~#c#~#a a#~#b#~#c#~#a
b#~#a#~#c#~#a b#~#a#~#c#~#a

The output I need is
a#~#b#~#d
a#~#b#~#c
b#~#a#~#c

View 12 Replies View Related

SQL & PL/SQL :: Alternative To Filer Initial Using Single REGEXP_REPLACE?

Feb 9, 2011

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;

View 8 Replies View Related

PL/SQL :: Delete Scott And Storage Information Same Time Using REGEXP_REPLACE?

Jul 11, 2013

I used REGEXP_REPLACE to delete Scott. is there any way to delete Scott and Storage information same time using REGEXP_REPLACE...? 

select REGEXP_REPLACE(dbms_metadata.get_ddl('TABLE','EMP'),'("Scott".)','', 1, 0, 'i') from dual; result: CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "SAL" NUMBER(7,2), "DEPTNO" NUMBER(2,0)) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGINGTABLESPACE "USERS"I want:CREATE TABLE "EMP" ( "EMPNO" NUMBER(4,0) NOT NULL ENABLE, "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "SAL" NUMBER(7,2), "DEPTNO" NUMBER(2,0))

View 18 Replies View Related

PL/SQL :: To Create Function Based Index For Group Function Columns

Jun 15, 2012

Is anyway to create function based index for group function columns.

For example

select max(timestamp),min(age),averge(sal).... ... .. from tab;

View 5 Replies View Related

PL/SQL :: Calling External C Function / ORA-06521 Error Mapping Function

Feb 4, 2013

I have the following C code:

class Factorial {
  public:
  int getVal (int a);
};
[code]....

/When I am trying to execute this function always get the ORA-06521. I changed the data types - but nothing changed.

Just in case, listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
                   (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Difference Between Stand Alone Function And Function Declared In A Package?

Mar 11, 2010

What is the Difference between a Stand Alone Function/Procedure & a Function/Procedure declared in a Package.

View 2 Replies View Related

SQL & PL/SQL :: What Is Advantage Of Deterministic Function Over Normal Function

Jun 10, 2010

What is advantage of Deterministic function over normal function?

What is the diff B/W Deterministic function and normal function and also give me a example in which scenario we use Deterministic function?

View 4 Replies View Related

Use MAX Function Without It Being In Results?

Apr 4, 2007

i have a query where i am using the max function to find the most recent record. What i want to do is use that query as part of an insert statement into a different table, however, i don't want to insert the column that i used the max function on. Is there anyway to use the max function without having the column it is being used on showing in the results?

View 1 Replies View Related

How To Use The NVL() Function In Statement

Mar 24, 2009

I have the following query.

sql

Original

- sql Code

SELECT class, COUNT (class)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)
GROUP BY class
ORDER BY class

SELECT class, COUNT (class) 
FROM nv_table 
WHERE l_id IN (          SELECT l_id            FROM n_table           WHERE id IN (1234)   
GROUP BY class   
ORDER BY class

It returns two columns: class and the total number of values in that class.

In some cases, there might not be any values under a certain class, so the query won't return anything.

In such a case I want the query to return 0. So what I want to see is:

class:A, COUNT(class):0

I'm trying to use the NVL function here, but either it doesn't work in this context, or it's not the correct syntax the way I'm writing it.

sql

Original

- sql Code

SELECT class, NVL(COUNT (class), 0)
FROM nv_table
WHERE l_id IN (
SELECT l_id
FROM n_table
WHERE id IN (1234)

[Code]....

View 3 Replies View Related

SQL & PL/SQL :: DML Operations Within Function

Dec 16, 2011

I am creating an stored function which has to do some inserts in the meanwhile, and return after all the work has done, an UDT (2 or 3 columns of NUMBER datatype).

With this scenario I have an problem. The DML operations are not supported by and "SELECT * FROM Table(MyProc(args))". I have to use this "SELECT * FROM Table(MyProc(args))" because I need to pass the stored function results directly to an dataset.

Using a Stored Procedure it gives no errors, but the arguments must be passed like OUT params, and it is not what I want.

My question is:
Is there any other way to get a result (UDT) of an Stored Function (that makes Inserts) into a DataSet?

View 12 Replies View Related

SQL & PL/SQL :: LAST-VALUE Analytical Function?

Sep 11, 2012

I'm posting below test case in which I'm not able to understand output for LAST_VALUE function. I'm expecting maximum value for the salary in a department. Because I'm partitioning by department and ordering a partition as assending so being last value it should give me maximum value within a partition i.e. department in this case.

CREATE TABLE EMP_MST
(
EMP_ID NUMBER(5),
EMP_NAME VARCHAR2(30),
CONSTRAINT PK_EMP_MST PRIMARY KEY(EMP_ID)

[code]...

View 4 Replies View Related

SQL & PL/SQL :: Have More Than One Blocks In Function

Jun 14, 2011

can i have more than one pl sql blocks in a function and can i use the variable of one cursor into another cursor of the same function?

View 2 Replies View Related

SQL & PL/SQL :: Regarding NVL Function Usage

Oct 20, 2011

There is an 'emp' table with a column name as 'mgr' with datatype 'number'. following is the detailed description of the table:

SQL> desc emp;

Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Now when I run the 'select mgr from emp e;' query the output which I get is:

7902
7698
7698
7839
7698
7839
7839
7566

7698
7788
7698
7566
7782

Note: One value in between here is null, the required to me is that I want to print a character value 'President' in place of null .

View 7 Replies View Related

SQL & PL/SQL :: How To Avoid Max Function

Feb 22, 2010

I have two tables where I have to find the record for Max value of the column sap_pkid for every sap_id as in given table create script. This script is giving correct value but looking for a better way so that when data increses it doesn't hit the performance.

way where max can be avoided or a more tuned query .

create table tab1 (sapid number,
denid number);
create table tab2 (sap_pkid number ,sapid number,
denid number,

[code]...

View 2 Replies View Related

SQL & PL/SQL :: Function To Sum Five Numbers

Apr 13, 2013

I am creating a function to sum five numbers (less 1). Is it possible to have an array of numbers in an SQL function, and how would this be implemented?

Here is the screenshot of my output (I cannot embed links until 5 posts!): flic.kr/p/eaSHBP

CREATE OR REPLACE FUNCTION sumfivenumbers ( n1 NUMBER, n2 NUMBER, n3 NUMBER, n4 NUMBER, n5 NUMBER)
RETURN NUMBER
IS
Sumnums NUMBER;
BEGIN
SELECT SUM(n1+n2+n3+n4+n5-1) INTO Sumnums FROM DUAL;
DBMS_OUTPUT.PUT_LINE(Sumnums);
RETURN 1;
END sumfivenumbers;
/
SELECT sumfivenumbers(5,5,5,5,5) AS "Five Numbers less 1" FROM DUAL;

View 7 Replies View Related

SQL & PL/SQL :: Function Not Calculating

Jul 15, 2011

I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code

create or replace function Get_Annual_Comp
(Sal in number, Commission in number)
return number
as
[code]...

When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.

View 9 Replies View Related

SQL & PL/SQL :: Function Without Parameter?

Aug 5, 2010

if function having retrun statement, why we need to use out or inout parameters.

View 16 Replies View Related

SQL & PL/SQL :: How To Use Decode Function

Oct 31, 2011

How can i use the decode function?

for example

I have the value of 1000 then the numbers 50-100 will be 'A' and 1-49 = 'B'?

View 9 Replies View Related

SQL & PL/SQL :: What Is Rowtocol Function

Dec 26, 2011

how to use rowtocol means a query.

View 5 Replies View Related

SQL & PL/SQL :: Decode Function

Apr 4, 2013

DECODE(:P_PERIOD_TYPE,'PJTD','PROJECT-TO-DATE','PTD','PERIOD-TO-DATE','YTD','YEAR-TO-DATE')

what does it mean..

View 3 Replies View Related

SQL & PL/SQL :: Function To Insert

Mar 22, 2013

Any way to write a function to parse through a clob and extract certain values to insert into a table. I've written the following and it compiles but it doesn't work.

create or replace function all_fields
(type_field VARchar2,
domain_field VARchar2)
return VARchar2 as
typefield VARchar2(100) :=type_field;
domainfield VARchar2(100) :=domain_field;

[Code]....

View 18 Replies View Related

PL/SQL :: Function Date

Aug 30, 2012

CREATE OR REPLACE FUNCTION my_to_date (value_in IN VARCHAR2)
RETURN DATE
IS
TYPE mask_t IS TABLE OF VARCHAR2 (30)INDEX BY BINARY_INTEGER;
fmts mask_t;

[Code]....

When i try using this method i am getting error...

SQL> select my_to_date(to_char(07/11/1987),'yyyy') from dual;
select my_to_date(to_char(07/11/1987),'yyyy') from dual
*

ERROR at line 1:ORA-06553: PLS-306: wrong number or types of arguments in call to 'MY_TO_DATE'

what is the error here...What is the solution for this function.....how to execute this function

When i am using ananymous block i am getting this answer:

SQL> declare
2 v date := '07-nov-1987';
3 u date;
4 begin

[Code]...

PL/SQL procedure successfully completed.

View 7 Replies View Related

SQL & PL/SQL :: Date Function?

Nov 22, 2011

i have one table chequedetails in this 4 rows are there that is

date name sno
1-10-2011 b 1
10-10-2011 c 2
25-10-2011 d 3
11-11-2011 e 4

these records are there in my table in this i selected between date it wil display o/p like this

date name sno
1-10-2011 b 1
11-11-2011 e 2

my query
select * from chequedetails where date between '1-10-2011' and '11-11-2011'

View 22 Replies View Related

PL/SQL :: Use Commit In A Function?

Sep 25, 2013

Can we use commit in a function?

View 10 Replies View Related

PL/SQL :: Lag Function In Oracle

Jul 15, 2012

I am using lag function to display values like below:

order details date starttime
----------------- -------- --------------
main order 1 07/10/12 06:00am
line 1 07/10/12 06:21am
line 2 07/10/12 06:31am
main order 2 07/11/12 07:00am
line 1 07/11/12 07:01am
line 2 07/11/12 07:02am

the data displays correctly when i use lag function except that the line 1 details are never getting displayed ie first line under every order does not get displayed? is using lag function in this case correct?

View 13 Replies View Related

PL/SQL :: Function Flow

Jul 9, 2012

explain the flow what exactly is the below function do in detail.

FUNCTION  Get_RmtUsr_rec_FUNC (p_MsgType IN VARCHAR2)
    RETURN    RemoteUser_Rec_Type;FUNCTION  Get_RmtUsr_rec_FUNC (   p_MsgType IN VARCHAR2)
RETURN    RemoteUser_Rec_Type
IS

[Code]....

View 12 Replies View Related

SQL & PL/SQL :: MIN Function In Oracle Sql

Sep 11, 2012

I have written a query which basically retrieves id and created date. IF i put MAX function it is returning id which have max created date. But if i use min function this query is not providing id with min created date,its not returning any rows.

SELECT To_char(OSH.osh_id),
OSH.osh_created
FROM tn_order_status_history osh,
tn_order_status_type ost,
tn_orderline_product op
[code]..........

View 4 Replies View Related







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