I have a scenario where I have to extract data in a string which is enclosed in [].
Here is what I want to do..
input:
[name_first] [name_mi] [name_last]
required output: this should return all the data that is enclosed in the brackets.
name_first
name_mi
name_last
I have tried the "REGEXP_SUBSTR" but my database version is 9i, and it is available only from 10g.
This is my sample data:
WITH table_data
AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL
UNION ALL
SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL
UNION ALL
SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL
[code].........
I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above.
I have a string like this .
'ABC-XYZ-MNO'
and i want the data in the below format
'ABC','XYZ','MNO'
To make SQL query to before and after specific character.
Create table test(flist not null VARCHAR2(200));
First field content with below record:
FC028CONNE_IMPORT_WRONG_COMP_LENGAPXXXXPPPP
FC024CALL_FUNCTION_OPEN_ERRORAPXXXXPP
FC025OPEN_DATASET_NO_AUTHORITYAPXXXXPPPPPPPPPPPPPP
FC015RAISE_EXCEPTIONAPAXEPPPPPPPPPPPPPPPPPPPP
to filter the above record from FLIST column thorugh sql script as below:
FC028< CONNE_IMPORT_WRONG_COMP_LENG> APXXXXPPPP
FC024< CALL_FUNCTION_OPEN_ERROR> APXXXXPP
FC025< OPEN_DATASET_NO_AUTHORITY> APXXXXPPPPPPPPPPPPPP
FC015< RAISE_EXCEPTION> APAXEPPPPPPPPPPPPPPPPPPPP
means remove first 5 charator and after APXXXXXXXXX.
Output of SQL query should come like below:
CONNE_IMPORT_WRONG_COMP_LENG
CALL_FUNCTION_OPEN_ERROR
OPEN_DATASET_NO_AUTHORITY
RAISE_EXCEPTION
I would like to extract only the values from the XML string. The xml string looks like
<addressId>1</addressId><addressSource xsi:nil="true"/><addressInfoName xsi:nil="true"/><addressLine1>245 Murray Ln SW Bldg 14</addressLine1><addressLine2 xsi:nil="true"/><addressLine3 xsi:nil="true"/><addressLine4 xsi:nil="true"/><phoneNumber xsi:nil="true"/><phoneNumberExt xsi:nil="true"/><city>Washington</city><county xsi:nil="true"/><stateOrProvince>DC</stateOrProvince><ZIPCode>20528-1002</ZIPCode><country>USA</country><congressionalDistrict xsi:nil="true"/></address>
And the expected output is
245 Murray Ln SW Bldg 14
Washington
DC
20528-1002
USA
I need to extract only the values from the XMl string using sql.
I have a string value like -- a,,b,c,d,e,f
Using just sql, I want to put each value of the above string in a different row. So the output should be --
a
b
c
d
e
f
using procedures it would not be that great but I want to do it just using queries.
I am trying to split comma separated string. My table has more than 5 lacks data. I have tried the following SQL but its taking more than 5 minutes. Any Alternative solution to return data quickly ?
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY REGEXP_SUBSTR(order_id,'[^,]+',1,LEVEL) IS NOT NULL
SELECT REGEXP_SUBSTR(order_id, '[^,]+', 1, LEVEL) order_id
FROM order_detail
CONNECT BY LEVEL <= LENGTH(order_id) - LENGTH(REPLACE(order_id, ',')) + 1
I have one table select * from abcd;
No err-----------------------------1 rishi,rahul2 rishi,ak I want output like:
No ERR1 rishi1 rahul2 rishi2 ak i am using the below query for this:
select no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but this query is giving me output:
1rishi1rahul2ak2rishi1rahul2ak if i am using distinct then only desired output is coming. select distinct no,regexp_substr(err,'[^,]+', 1, level) from abcd connect by regexp_substr(err, '[^,]+', 1, level) is not null but i don't want to use distinct because my table has millions of rows and err contains comma separated varchar(6000);
I have a requirement to sort a comma seperated string. For example if I pass '1234,432,123,45322,56786' as string, then it should return '123,432,1234,45322,56786', after sorting the numbers inside the string.
I have done it creating Global Temporary table. Is there a way without creating the Temp table. I understand I can write the whole logic to sort and append the string, but if there is any direct way.
CREATE GLOBAL TEMPORARY TABLE TEMP_TAB(COL1 VARCHAR2(100)) ON COMMIT DELETE ROWS;
CREATE OR REPLACE FUNCTION func_sort_string(pi_string IN VARCHAR2, pi_delimiter IN VARCHAR2 DEFAULT ',')
RETURN VARCHAR2 IS
PRAGMA AUTONOMOUS_TRANSACTION;
l_str VARCHAR2(2000) DEFAULT pi_string || ',';
[code]...
I need writing sql which can return the Count of Comma's in a string. Here is my table and data
CREATE TABLE TEST1(SNO NUMBER,STR1 VARCHAR2(30));
INSERT INTO TEST1 VALUES(1234,'ABCD,LL LT,MP');
INSERT INTO TEST1 VALUES(1456,'PP MR');
INSERT INTO TEST1 VALUES(1589,NULL);
INSERT INTO TEST1 VALUES(1897,'PP MR,FTR CLR ON');
Here is the output I am expecting
SNO STR1 STR1_COUNT
1234 ABCD,LL LT,MP 3
1456 PP MR 1
1589 0
1897 PP MR,FTR CLR ON 2
Basically I need to the count of Words separated by comma
I have a table test with column containing dates, characters and numbers. I have to extract the number part and the three characters before the number . My data looks like :
TEST
ID DATA
1 3/12/2007
2 0
3 3/8/2010 ABC 217
4 NONE
5 COLM XYZ 469 6/8/2011
6 LMN 209
My expected results should look like :
ID DATA
1
2
3 ABC 217
4
5 XYZ 469
6 LMN 209
I have come one requirement where i need to extract data from a LONG RAW data type column.
View 7 Replies View RelatedCurrently, I am running 4 separate queries in order to retrieve specific data from a XML file. Is there a way of extracting all 4 values via XML tags in a single query - e.g. :
Message Reference UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
456123 >(Cancelled)</UploadError>
456123 >4561</UserId>
456123 >1234</SecurityIdentifier
At the moment, I am extracting the required info as follows.
Example query 1: message_content, 2000, 2303 > retrieves starting point for an error header
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 2303))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
Once retrieved, I transfer to Excel and use a formula to extract the specific header (e.g. using =MID(B1,1,11))
Example query 2: message_content, 2000, 581 > retrieves the starting point for a user id.
select ml.message_reference, UTL_RAW.CAST_TO_VARCHAR2 (dbms_lob.substr(message_content, 2000, 581))
from table.msg_archive ma, table.msg_log ml
where ma.message_id = ml.message_id
and ml.message__cd = 'MP_XML'
and ml.message_reference in (456123)
DB Used : Oracle 10g.
A table X : NUM, INST are column names
NUM ----- INST
1234 ----- 23,22,21,78
2235 ----- 20,7,2,1
1298 ----- 23,22,21,65,98
9087 ----- 20,7,2,1
-- Based upon requirement :
1) Split values from "INST" Column : suppose 23
2) Find all values from "NUM" column for above splitted value i.e 23 ,
Eg:
For Inst : 23 ,
It's corresponding "NUM" values are : 1234,1298
3) Save these values into
A table Y : INST, NUM are column names.
INST NUM
23 1234,1298
1) I have a thousand records in Table X , and for all of those records i need to split and save data into Table Y.Hence, I need to do this task with best possible performance.
2) After this whenever a new data comes in Table X, above 'split & save' operation should automatically be called and append corresponding data wherever possible..
I need to extract tag values from REST web service response.
Webservice response
Quote:
<?xml version="1.0" encoding="UTF-8" standalone="yes" ?>
- <LicenseDetailsRes xmlns="com:*******services:LicenseDetailsRes">
<ResponseCode>LD001 - SUCCESS</ResponseCode>
<OrderNumber>51663852</OrderNumber>
<LineNumber>8</LineNumber>
<quantity>0</quantity>
</LicenseDetailsRes>
I am storing above response in a table column 'output_xm' of xmltype datatype.
My requirement is to extract 'ResponseCode' & 'quantity' values from above response. For which I am using below query but it's not working.
SELECT x.order_number
,x.line_number
,x.tnc_pid
,EXTRACT (VALUE (d), 'LicenseDetailsRes/ResponseCode/text()').getstringval () status
,EXTRACT (VALUE (d), 'LicenseDetailsRes/quantity/text()').getstringval () pak_quantity
FROM xxcfi.xxcfi_vt_tnc_pak_details x
,TABLE (XMLSEQUENCE (EXTRACT (x.output_xml, '/LicenseDetailsRes'))) d
WHERE x.order_number = 50403901
AND x.line_number = 2
AND x.tnc_pid = 'R-LMS-4.0-100-K9';
I exported and imported data from one oracle database to another, but not all the data got loaded in to the destination database. Basically it is filtering the data. Can it be because of the reason that the sql developer may be an express edition?
View 4 Replies View RelatedI have an XML of the following format:
<data>
<students>
<student>
<studentname>Raymond<studentname>
<StudentId>1</StudentId>
<StudentAge>11</StudentAge>
<StudentMark>0</StudentMark>
</student>
</students>
Now i have to insert this xml into DB , the table consist of following columns ( row number , property name , value ) Expected out put is (1,student name,Raymond) ,( 1, studentid , 1) ( 1, studentAge, 11) (1,Studentmark , 0) The challenges here is
1. how to get the tag names and populate the property name column ?
2. The number of properties for a student can be variable , How can i deal with them ?
I need to extract the values from XML file.I tried with method described in below links -
[URLs]....
My XML file is -
<?xml version="1.0" ?>
<!DOCTYPE main [
<!ELEMENT main (DATA_RECORD*)>
<!ELEMENT DATA_RECORD (STATE_CODE?,JOB_NAME?,WC_CODE?,JOB_ID?,STATUS?,LOG_MESSAGE?)+>
<!ELEMENT STATE_CODE (#PCDATA)>
[code]...
The query is not returning any rows.
Below is the function code used to extract data from blob column. The function works fine when the blob data length < 2000 bytes. When it is more than, it is throwing an error as below.
Table name: mr_test
Columns: id number
seo blob
CREATE OR REPLACE FUNCTION fn_mr_blob(in_id IN number) return varchar2
IS
len NUMBER;
[Code]....
ORA-01489: result of string concatenation is too long
when I replcae the
"SELECT myvar||trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;"
with
SELECT trim(dbms_lob.substr(seo,bytelen,vstart)) into myvar FROM mr_test WHERE id = in_id;
myvar1 := myvar1||myvar;
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
Enter user-name: sys@testdb as sysdba
Enter password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select deptno,wm_concat(ename) from scott.emp group by deptno
2 /
DEPTNO
----------
WM_CONCAT(ENAME)
--------------------------------------------------------------------------------
10
CLARK,MILLER,KING
20
SMITH,FORD,ADAMS,SCOTT,JONES
30
ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD
SQL>
i want to display this data with semicolon seperated not comma seperated.
I have a table with three columns X, Y and Z.The data in Column z is of the type 20/1425SE, 13/1235NW.Is there a way to split the data entries where Z LIKE '%/% and insert them as two separate rows.
I don't want to have any entries with '/'. Can these be deleted along with splitting the data entries?
I'm creating a package function that would return the image from the table HR.PER_IMAGES.
Here's the table description of HR.PER_IMAGES
IMAGE_ID NUMBER(15)
IMAGE LONG RAW
PARENT_ID NUMBER(15)
TABLE_NAME VARCHAR2(30)
I have also created a simple PL/SQL code that would supposed to extract the value of the IMAGE column and store it in a variable then return it from a function.
DECLARE
x LONG RAW;
BEGIN
SELECT image
INTO x
FROM per_images
WHERE parent_id = :p_parent_id
;
END;
However, I'm getting the "ORA-06502: PL/SQL: numeric or value error" message. I have tried retrieving an specific image using Oracle Reports Developer and when I queried it was able to render the image on the report page.
How to extract date from a timestamp data
for example
Input-15/06/2010 05:30:00.000000
output: 15/06/2010
I have two tables.
First one is
STAFF
======
STAFFNUM NAME
======== ====
1 A
2 B
3 C
SUBJECT
=======
SUBCODE SUBNAME LECTURER
======= ======= ========
A1 ABC 1
A2 EFG 2
A3 HIJ 1
I did the following query
SELECT STAFF.STAFFNUM, STAFF.NAME, SUBJECT.LECTURER
FROM STAFF,SUBJECT
WHERE STAFF.STAFFNUM NOT IN SUBJECT.LECTURER
It will show me..
B and C is not teaching A1.
A and C is not Teaching A2.
B and C is not teaching A3.
This is not what I want.
What I want is to show who is not teaching any subjects.So the expected result is only C coming out.
Using UTL_TCP package (for the first time; maybe there's smarter way to do that), I captured contents of a certain web page. The page contains (along with some text, images, etc.), a list of values I'd like to extract. Something like this:
PRVI miravira pond 23.7.2010 102,2221
NEKI miravira pond 23.7.2010 105,0996
DRUGI miravira pond 22.7.2010 101,3789
The result (output) of the PL/SQL procedure contains several hundreds of lines. I narrowed the output to a part which I'm interested in. It looks like this (a screenshot, because [code] or [pre] tags won't allow me to paint text, while the others don't preserve formatting):
Red values are what I'm looking for. The first one represents a date (July 23rd 2010), and the second one is amount (105,0996).
Now, what's the problem: the above mess doesn't look the same every day (probably because the rest of data that appears on a web page changes too). Therefore, SUBSTR (with hard-coded positions) that seems to be working today is wrong tomorrow. Moreover, IF condition I used might not provide desired part of a web page every time.
Here's the code (modified a little bit, unimportant for what matters):
declare
c utl_tcp.connection;
n number;
buffer varchar2(255);
l_x_dat varchar2(10);
l_x_izn varchar2(10);
[code].....
L_X_DAT and L_X_IZN are values I need. SUBSTR parameters, as I said, are far from being good.Is there any smart way to extract those two values? What (Oracle) technology could be used here? Or should I just try to, somehow, set SUBSTR values correctly (dynamically)?
If there was a way to uniquely fetch those values (instead of getting the whole web page and digging for what I'm interested in), it would be extraordinary.
I have requirement of extracting the relationships between all tables in the Database(Oracle 10.2.02) created by CA Unicenter Servicedesk.
Tell me about any tools/scripts which I can use to extract this information. I can do it manually, but it is time consuming.
In my DB there are special characters are stored like "Świętochłowice". So in Unix script while extracting these characters, I have used
export LANG="universal.UTF-8" in order to make it English. which works fine.
But my question is how to retain this special character intact as, it is in DB?
So in UNIX script
export LANG="universal.UTF-8"
sqlplus -s uname/pwd@hostname/schema << ENDSQL | sed -e "s/Connected.//" -e "/^$/d"
set pagesize 0 feedback off verify off heading off echo off serveroutput on size 10000
spool /path/out.txt
Select name from tablename where is=12;
spool off;
exit
ENDSQL
Output is "Swietochlowice" (makes sense),but how to get the output as "Świętochłowice" which is in DB? I have tried different NLS_LANG option, but no success.
I am having two XML's in my table.
XML1 =
<?xml version="1.0" encoding="UTF-8" ?>
<Twist xmlns="URL...." xmlns:ns2="dsig:URL....xmlns:dsig="URL....
xsi:schemaLocation=URL....
xsi:type="ElectronicBillingMsg">
<messageId>TWISTMSG</messageId>
</Twist>
[code]...
I am using the below query to extract value from XML.
SELECT extractvalue(xml_column, '/Twist/messageId') MsgId FROM mytab;
When i run this query on XML2, it gives the output.But when the same query is fired on XML1, its not returning the message id.
Create table a ( Objectid number, Value varchar2(2000);
/
Insert into a values (12, '2,3,4');
Insert into a values (13, '8,7,4');
Insert into a values (14, '3,8,9');
Insert into a values (15, '6,3,11');
I should get the output as:
ID Value
------ ------
12 2
12 3
12 4
13 8
13 7
13 4
14 3
14 8
14 9
15 6
15 3
15 11
I have created and formatted a mini test scenario. Execute the scripts I have below?
From the column adj_second_line, I am trying to clip everything from the characters '201' all the way to the end...or the NUMERIC start value after the word 'TYPE'. Whichever way is easier for you...
create table test_split
(adj_second_line varchar2(80))
insert into test_split
values ('ADJ#1-2G3AYL TYPE 20100501 20100524 0MO/23DY')
insert into test_split
values ('ADJ#2-656GYP TYPE AR 20100522 20100524 0MO/15DY')
insert into test_split
values ('ADJ# 265HKK TYPE X 20100428 20100524 0MO/30HT')
insert into test_split
values ('ADJ#13 43327DR TYPE AJ 20100413 20100524 0MO/30HT')
-- Need to have another column called split_second_half
SELECT adj_second_line,
substr(adj_second_line, 1, instr(adj_second_line, '201')-1) split_first_half,
instr(adj_second_line, '201') clip_from_position
FROM test_split
--Desired output for the split_second_half column
20100501 20100524 0MO/23DY
20100522 20100524 0MO/15DY
20100428 20100524 0MO/30HT