SQL & PL/SQL :: Extract String Between The Braces?

Sep 13, 2011

I have following column data

NZ(abc_mode)/ (NZ(bch_mode) + NZ(cdh_mode)

Now i need query to extract the string inside the braces NZ() with or without using regular expressions in SQL

the output should be

abc_mode
bch_mode
cdh_mode

View 5 Replies


ADVERTISEMENT

SQL & PL/SQL :: Extract Date From String

Jan 8, 2013

I need to extract the First portion of date entered in remarks column , i have prepared a test as below.

CREATE TABLE LEAVE_DYS (LV_EMP_CODE VARCHAR2(12), LV_DESC VARCHAR2(2000));
INSERT INTO LEAVE_DYS VALUES ('R0754','LEAVE SALARY FOR APRIL 16, 2010 - APRIL 15, 2012 (33 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0667','LEAVE SALARY FOR JULY 14, 2009 - JULY 13, 2011 (44 ABSENTS)');
INSERT INTO LEAVE_DYS VALUES ('R0841','LEAVE SALARY FOR MAY 29, 2010 - MAY 28, 2012 (NO ABSENT)');
INSERT INTO LEAVE_DYS VALUES ('R0854','LEAVE SALARY FOR MAY 29, 2010 - MAY- 28, 2012 (1 ABSENT)');
[code].....

--Required output is

LV_EMP_CODE LV_DESC
R0754 16/04/2012
R0667 14/07/2012
R0841 29/05/2012
R0854 29/05/2012
R0313 09/09/2012

View 15 Replies View Related

PL/SQL :: Extract Number From String

Oct 21, 2012

I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:

SELECT  regexp_replace (regexp_replace ( REGEXp_REPLACE ('  !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 |  " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','')  FROM dual
{code}

Is there a more efficient way to get this done ?

View 4 Replies View Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

I have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.

View 3 Replies View Related

PL/SQL :: Extract Values From String To Variables?

Jul 16, 2013

I need to extract values from string to variables as below.

declare
str varchar2(100):='Acknowledgment=1234,Order Requester=5678,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
l_a varchar2(100);
l_or  varchar2(100);
l_s  varchar2(100);
l_ot  varchar2(100);
l_pre  varchar2(100);
Begin
l_a:='1234';
l_or:='5678';
l_s:='9999';
l_ot:='1456';
l_pre:='1234,23445,56767';
end;
/

But here challenge is order of alignment change dynamically. ex as below. 

str varchar2(100):='Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456,Pre=1234,23445,56767';
str varchar2(100):='Pre=1234,23445,56767,Order Requester=5678,Acknowledgment=1234,Site Contact=9999,Other Contact=1456';

 So how to extract Acknowledgement to l_a,Order requester to l_or ...

View 9 Replies View Related

SQL & PL/SQL :: String Function - Extract Domain From Email Address

Oct 12, 2011

I'm trying to extract the domain from email addresses like from clay500@msn.com I want to pull msn

I figured I would use the POSITION function, but it fails, even when I cut and pasted the example from the syntax guide

SELECT POSITION ('CAT' IN 'CATCH') FROM DUAL;

the guide says you get the answer 1

but I get ORA-00907: missing right parenthesis

What gives?

View 8 Replies View Related

SQL & PL/SQL :: How To Extract First 3 Characters From Each Word In A String / Sentence And Separate With Underscore

Oct 22, 2013

I have some strings like

'Net Amount Payable by an Individual',
'Net Amount Payable by an Individual+Tax',
'Total Amount Payable towards Service',
'Total Amount Payable towards Service.+Tax'

I need to extract the first three letters from each word and separate them using an underscore. The output should be as follows for the above strings -

Net_Amo_Pay_by_an_Ind
Net_Amo_Pay_by_an_Ind_Tax
Tot_Amo_Pay_tow_Ser
Tot_Amo_Pay_tow_Ser_Tax

I request to let me know the way to get the ouput as mentioned.

View 4 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

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

SQL & PL/SQL :: How To Extract Data Between [ ]

Jun 8, 2012

I have a requirement where the data between [] or ][ has to be extracted from a string.

Here is my situation :

INPUT:
[abc] [def]-[ghi][jlk]

OUTPUT:
row_num field_name
1 abc
2 <blank_space>
3 def
4 -
5 ghi
6 null
7 jkl

View 5 Replies View Related

SQL & PL/SQL :: Extract One Schema's DDL

Apr 22, 2011

Is there any option available in DBMS_METADATA.GET_DDL in such a way that I can extract the script (user creation+grants)only for that particular schema?

View 5 Replies View Related

SQL & PL/SQL :: Extract DDL Of DBMS_JOB

Dec 20, 2011

How to extract DDL of DBMS_JOB in sqlplus ?

View 30 Replies View Related

SQL & PL/SQL :: How To Extract CSV File

Jun 14, 2010

I have to extract a csv file running a sql file.

SQL>@d: estEndItem_Vio_Item_Material_Violations.sql;

This works on the sql prompt. I have to do the same using schedular now for which I want to do the same embedded in a procedure.

create or replace procedure test_csv
as
begin
@d: estEndItem_Vio_Item_Material_Violations.sql;
end;

How can I run the sql file in a procedure.

View 4 Replies View Related

PL/SQL :: Extract Value From Column Name

Nov 14, 2013

I'm stuck on 1 scenario

I have the following table: 

Create tabledrop table age_rate;
CREATE TABLE age_rate(age_0_4 NUMBER(4),age_5_20 NUMBER(4),age_21_34 NUMBER(4),age_35_44 NUMBER(4));
-------------------------------Insertion 
INSERT INTO age_rateSELECT 45, 50,  60,  90
FROM dual UNION ALLSELECT 45, 50, 60,  88
FROM dual UNION ALLSELECT 40, 50,  60,  90 FROM dual UNION ALLSELECT 5,  50, 60,   88
FROM dual ;  
-------------------------------Query on table
SELECT * FROM age_rate; Query Output age_0_4           age_5_20      age_21_34      age_35_44 45                          50                     60                     9045                          50                     60                     8840                          50                     60                     905                            50                     60                     88 Required outputRate     Min_age     Max_age

----The below rate is for age band 0_445   0           445            0           440            0           45              0           4

--The below rate is for age band 5_2050            5           2050            5           2050            5           2050            5           20

--The below rate is for age band 21_3460            21          3460            21          3460            21          3460            21          34

--The below rate is for age band 35_4490            35          4488            35          4490            35          4488            35          44   

Rules--I have all data in rows so each column in row create separate rows and add 2 columns automatically Min_age and Max_age and insert value on these column on basis of column name for example  if column name like age_0_4 then put 0 in min_age and put 4 in max_age means values for Min_age and Max_age extract from the basis of column name. I don't know if it is possible or not

View 6 Replies View Related

PL/SQL :: Query To Extract Changes

May 7, 2013

I am using Oracle database version 11.2.1 and would like to extract the level change and level start date where reason_code is 'PROMO' split by ID.

The test script is below:

create table test(
id number,
start_date date,
reason_code varchar2(10),
level number
)
insert into test values(001, '01-JAN-13', 'PROMO', 2);
[code]....

The expected output would be:

Fields - ID, old_level, old_level_start_date, new_level, new_level_start date

e.g.
001, 2, '08-MAR-13' , 3, '05-MAY-13'
002, 4, '13-APR-13' 5, 02-MAY-13'

View 2 Replies View Related

PL/SQL :: Extract Numbers Only

Sep 18, 2012

Here i face probelm that he numbers must be follw by DOT "." , this is not correct if the statment only conatines numbers without DOT that not extract. As the

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but mean i have to add . after numbers . as follow

SELECT REGEXP_SUBSTR ( 'hello to 8898989898989 jkjk nnnm mnj'
, '([0-9]+.[0-9]*)' || -- Starts with digit(s) (may or may not have digits after .)
'|' || -- or
'(.[0-9]+)' -- starts with decimal point
) AS result
FROM dual
;

but this is not right

i want to extract numbers without DOT also.

View 6 Replies View Related

Extract All Employees Who Have 2 Functions

Feb 24, 2013

I have a table:

create table employee_function
(
id_emloyee number,
id_function number
);
with clients and their functions.

I want to extract all employes who has 2 functions (ex:id_function = 1 and id_function=2)

View 3 Replies View Related

Extract Hour From A Date?

Mar 29, 2004

how to extract hour part from a date?

suppose my date is like

29-mar-2004 09:20:34

i wanna get only hour from the above date-

View 3 Replies View Related

How To Extract The Data From DBF Files

Aug 13, 2010

I'm a SAP consultant working in SQL on NT platforms. This is the first conversion from Oracle that I have done. My client has provided us with a "Cold" backup of the Oracle dbase on a HD formatted in Unix, I have the partition mounted and I'm able to view the files. I have the ORDATA folder with all the .DBF files.

Q: How do I extract the data from the .DBF files. I need to export to something workable with SQL.

Original database was on Unix, I'm operating on Windows platform.

View 4 Replies View Related

Creating Extract File From DB?

Jun 22, 2010

I have data in multiple oracle tables. I have to create a extract flat file after applying some validation and business logic on it and store it in unix server with naming convention FF_RMS_SC_<<YYYYMMDDhhmm>>.txt.This job will be scheduled to run daily to create the flat file. I guess pl/sql and unix needs to be used.

View 1 Replies View Related

Unable To Extract NULLS

Dec 20, 2011

Am trying to extract data from oracle onto flatfile(.txt), am using UTL_FILE but, NULLs in oracle tables are getting converted into space and if i try loading into table it is getting loaded as space,

View 1 Replies View Related

Extract DDL's Without Storage Parameters?

May 11, 2005

I need to extract DDL's without storage parameters. if i use the export and import using indexfile or if i try to extract using the DBMS_METADATA.GET_DDL package , in both ways my output is with the storage parameters

eg:
CREATE TABLE "SEPT"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),

[Code].....

View 6 Replies View Related

Extract Index DDL From Database?

Apr 27, 2011

Is there a way, i can extract index DDL from my database?

View 1 Replies View Related

SQL & PL/SQL :: How To Extract Substring From A Set Of Strings

Jan 25, 2012

Im trying to extract, *THIS IS MY STRING* from *<YUVRAJ THIS IS MY SRTING YUVRAJ>* .

In this <YUVRAJ and YUVRAJ> is constant, need to remove which is being appended at begin and end for a set of strings.

View 3 Replies View Related

SQL & PL/SQL :: How To Extract The Rights Of Users On ERP

Mar 2, 2013

I would like to extract the user rights from the ERP that I use with SQL/Oracle.

There are 3 databases. The first gives me all the users with the main key id_user. The second one gives me the actions with the main key id_action. The third one only gives me what the user can't do.

I would like to know all the actions that all users can do or not.

The result must be something like:

Action 1
User 1 Yes
User 2 No

Action 2
User 1 No
User 2 Yes
....

View 9 Replies View Related

SQL & PL/SQL :: Extract Data Into Excel

Mar 21, 2012

Data that enter by user in database having " in the sentences
example:

column1
----------
"My Smurf Village

column2
--------
Farmvile

column3
--------
"Testing

My select statement is

Select column1,column2,column3 from table. Output in toad perfect but When I export into excel using pipe | as a separator, the output become

column1 in excel
----------------
My Smurf Village|Farmvile

column2 in excel
---------------
Testing

How I can code my program so that when extract to excel " is recognized as sentences. output are correct as in database?

View 5 Replies View Related

SQL & PL/SQL :: Extract Minute From Time?

Sep 7, 2010

1) Extaract time from date field

2) Extract minute form that time ..

View 13 Replies View Related

SQL & PL/SQL :: Extract Data - REGEXP?

May 25, 2012

How to extract data from the below string.
END OF FILE. ROW COUNT: 23,9
Output should be divided into 3 parts

END OF FILE. ROW COUNT:
23
9

View 8 Replies View Related







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