I need to split the given string into muliple sub strings based on one special character
Ex : Speace is Special character
with data as (
select 'ab cd ef gh ' from dual )
select * from data
Required Output :
ab
cd
ef
gh
i have a column 'name' in which value is 'Shailesh Negi',i have to insert 'shailesh' into'first name' column and 'Negi' into 'last name' column respectively.
View 20 Replies View RelatedI was wondering if there is an Oracle function available to split a string based on a delimiter character. For example, if I have a table consisting of:
HOST
-----
emerald.test.com
ruby.test.com
diamond.test.com
I would like to only return ('emerald', 'ruby', 'diamond') by getting all data leading up to the first '.' character.
I have to fetch a string which is between to constant strings in a column.
Ex: Test Column
"The Student Record 10101 is deleted"
"The Student Record 10102 is deleted"
"The Student Record 10103 is deleted"
3 rows.
In this i need to fetch only ID from each row.
create table testtable ( TestCol varchar2(4000));
INSERT INTO TESTTABLE VALUES ('The Student Record 10101 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10102 is deleted');
INSERT INTO TESTTABLE VALUES ('The Student Record 10103 is deleted');
I have a table Product as;
desc product
Name Null Type
--------------------------------------------------------------
PRODUCT_ID NOT NULL NUMBER
INGREDIENT VARCHAR2(20)
The data in Ingredient is separated by ','.
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A,B,C
2 A,D
3 E,F
I need to write a sql statement which will retrieve a pair of product and ingredient in each row as;
PRODUCT_ID INGREDIENT
---------------------- --------------------
1 A
1 B
1 B
2 A
2 D
3 E
3 F
write this sql ?
Table Name : Trans
chitta_enn number(10,0)
varavu_patti varchar2(100)
pattru_patti varchar2(100)
Thogai number(10,2)
where in the data's are as follows
chitta_enn varavu_patti pattru_patti Thogai
101 panam null 101.00
101 null sambalam 51.00
101 null kamishan 50.00
I need to create the view as follows
View Name : Pattiyal
vivaram varchar2(2000)
varavu number(10,2)
pattru number(10,2)
The view data should get display as follows
vivaram varavu pattru
sambalam kamishan null 101.00
panam kamishan 51.00 null
panam sambalam 50.00 null
Logic:
Each table row will have only one value either in varavu_patti or in pattru_patti. On selecting the row, thogai must be posted in varavu when varavu_patti is not null or should be posted in pattru when pattru_patti is not posted.on selecting the table row, vivaram should contain all other rows varavu_patti and pattru_patti on equating chitta_enn
Is it possible to create a view as above
I have a table which has a column that stored concatenated data.
Sample test case is as below:
SQL> create table tst (
2 col1 varchar2(20));
SQL> insert into tst values ('one,two,three');
1 row created.
SQL> commit;
Commit complete.
Is there any way i could write a sql to split the text of this column into rows? Sample output im expecting is as below
col1
-------
one
two
three
I need to join ISSUED_REMOVED Table with ITL Table. having each quantity each row.
Eg. If a unit Serial no '354879019900009' has a part (1015268) issued 8 times and then unissued 4 times so finally the part was issued 4 times. so I need 4 rows to show for each qty 1 for that part and unit serial number.
-- ITL Table
Create table ITL_TEST (
ITEM_SERIAL_NO, ITEM_BCN, ITEM_ID, ITEM_PART_NO, OPER_ID,
ISSUED_REMOVED_PARTNO, ISSUED_REMOVED_QUANTITY, QUANTITY, SHIPMENT_ID)
[code]....
-- Issued Removed table
create table ISSUED_REMOVED_ITEM
(REPAIRED_ITEM_ID, ISSUED_REMOVED_ITEM_ID, ISSUED_PART_ID, OPER_ID, ISSUED_REMOVED_QUANTITY)
as select
122013187, 1323938, 1015268, 308, 2 from dual union all select
122013187, 1323939, 1015269, 308, 2 from dual union all select
122013187, 1323940, 1015268, 308, 2 from dual union all select
[code]....
-- The way I need to join the Issued_Removed Table
select * from ITL_TEST ITL
left join
issued_removed_item iri
on iri.REPAIRED_ITEM_ID = ITL.ITEM_ID --ITL.ITEM_ID --rlsn2.item_id --126357561
and iri.oper_id = 308 --in ( 308, 309)
[code]....
I see that I can use SDO_LRS.SPLIT_GEOM_SEGMENT to split a line at a single point (and get 2 resulting lines).
However, how I could split a line, at multiple points, into multiple segments? I need to do this for many rows, therefore a function or procedure would be good if any exists.
I need to split a column into multiple columns. The data in my column is separated by a Comma (,). But the data is dynamic and I could have any number of data separated by (,).
Quote:FOR Ex:
If COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION, ITEM_DATA_TYPE
RESULT: should be 4 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION and ITEM_DATA_TYPE
Is COL1 contains
CRITERIA_ITEM_TYPE_ID, CRITERIA_ITEM_TYPE, DESCRIPTION
RESULT:
should be 3 columns contains the values
CRITERIA_ITEM_TYPE_ID and CRITERIA_ITEM_TYPE and DESCRIPTION
I have a function in PL/SQL that uses CTE to obtain several strings. I need to return those strings to C#, either as a Return Value or a OUTPUT parameter.
I've managed to do that using PLSQL Associative Array, but it has the unnecessary usage of Array Bind Size, and I do not need to know what will be my Array size. Is that any other way to do it?
My Package is the next one:
create or replace
PACKAGE Pkg1 IS
TYPE listResults is TABLE of VARCHAR2(100) INDEX BY BINARY_INTEGER;
PROCEDURE CalculateResults ( iCode IN VARCHAR2, iAg IN VARCHAR2, resultados OUT listaResultados );
END Pkg1;
create or replace
PACKAGE BODY Pkg1 AS
PROCEDURE CalculateResults(
iCode IN VARCHAR2,
[code]......
Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
How block splitting will happen in oracle block.
Suppose, I am having Oracle BLOCK_SIZE of 16k and my Linux OS level BLOCK SIZE is 4k. then How 16k oracle block will store in OS level? and
What will be the internal block splitting process?
I need to dump the contents of a very large table into text files for archiving as we retire this old DB. The table has about 16 million rows, and a few of the columns are up to 4000 characters wide (varchar2(40000)). I've got 2 problems:
1) How can I select records that occur in a certain month of a year (there is a date column) and put the selected records into a file?
2) I don't have access to the server OS, so UTL_FILE is not possible. The output is also so large that I'm having trouble with the DBMS_OUTPUT.PUT_LINE.
I'm trying to get the first block of the IF working first, so the rest is just placeholders.
DECLARE
v_mm number (2);
v_yyyy number (4);
min_mm number (2);
min_yyyy number (4);
max_mm number (2);
max_yyyy number (4);
min_date date;
[code]....
Aim: Architecture change in existing application
Domain: Health Care
Background: There are 2 application ( Front end: one in oracle forms - deals with accounts module and another in some legacy application - deals with patient, clinical and diagnose module) using and sharing the same Oracle 9i database.
Patient related modules are moved into another database ( java as a front end, oracle 10g as backend ) which is normalized - eliminating duplicate tables and column, also its tables and columns names are not matching with existing (patient)system.
Now the requirement is making the existing application related only to Accounts module ( having complicated business logic written in packages ) to work as it is without changing the code, design drastically.
Questions:
1. Now how best this task can be completed without affecting existing Accounts system drastically ( with minimal changes )?
2. what are the possible best approach to achieve this ?
3. what are the best way for communicating the 2 DB in this scenario ( may be creating synonym, views etc ) ?
4. What are challenges that needs to be addressed ?
I have string like 'PRASAD,ALLEN,STEWART,SMITH'.
LIKE
COL1 COL2 COL3 COL4
-------------------------------
PRASAD ALLEN STEWART SMITH
I want to store the data into columns using SELECT statement only
11.2.0.3...just trying to learn the syntax. I have not worked with IOTs and I am exploring a feature I have not really used to try to learn something new. I know about intervals.This exact split syntax below, works on a heap table without errors. When I run the following split against a regular heap table it works.
CREATE TABLE MYTABLE (
INSERT_DATE date,
myfield1 varchar2(50),
myfield2 varchar2(50),
myfield3 varchar2(50),
[code]....
alter table MYTABLE split partition "FUTURE" at ( to_date('09_MAY_2013_13','DD_MON_YYYY_HH24' ) ) into ( partition "B4_09_MAY_2013_13", partition "FUTURE" ) update global indexes
*
ERROR at line 1: ORA-00932: inconsistent datatypes: expected BINARY got NUMBER
Any utility that will take in a string and then search through multiple Oracle Forms and Reports modules (.fmb, .rdf, .mmb, .plx, etc). simultaneously to find which modules have strings that match the search criteria ?
To do this on an individual form is easy enough using the Oracle Forms Developer editor however I have over 100 Forms and reports.
I would like need a utility that will search all Oracle Forms in a given directory without having to actually open a Form(s) in the Oracle Forms Developer editor and then report which Forms may contained the sought after string.
I have requirement wherein i need to compare two strings (with multiple words) and it should return the %(percentage) of comparison.
e.g. "oracle infotech" and "infotech oracle" are 100% match
Do we have any oracle built ins to compare ?
PROCEDURE COLUMN_SPLIT (p_def IN VARCHAR2, p_sch OUT VARCHAR2, p_table OUT VARCHAR2, p_column OUT VARCHAR2)
IS
BEGIN
NULL;
END;
END;
I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column for example p_sch will be like hello.howare.you.I want to split it to hellohowareyouI have very limited knowledge with pl/sql.
The db field is a string-type field that hold strings such as:
'1234'
'753'
'textstring'
'345'
Obviously, if you sort it, it'll be stored as a string such as:
'1234'
'345'
'753'
'textstring'
My client wants it so the numbers sort as integers, followed by string-like strings (sorted alphabetically), so it's like.
'345'
'753'
'1234'
'textstring'
Is there a quick and dirty SQL-only way to doing this in Oracle?
I have 2 strings that I want to compare for example
string1 = 'ABC~AB/10/1234'
string2 = 'ABC~AB/10/1234~.....'
There could be anything after the 2nd ~ in string 2 is there a easy way of trimming string2 to the first 14 Characters? Or do I have to find the 2nd instance of ~ and then remove everything after (and including) that?
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.
In the code segment below (hope it appears right) I can understand the use of single quotes in the first two examples but in the third example below I had to use double quotes around the word - Today's - and I not sure I understand why?! I'm aware of the rules ...If you want a single quote to appear in the middle of a string add another single quote to it.If you want a single quote to appear at the beginning or end of a string add 2 single quotes to it.If you want a single quote to appear on its own add 3 single quotes to it.
SQL> select 'This isn''t' from dual;
'THISISN''
----------
This isn't
SQL> select to_number('34@456#789', '999G999D999', 'nls_numeric_characters=''#@'' ') from dual;
TO_NUMBER('34@456#789','999G999D999','NLS_NUMERIC_CHARACTERS=''#@''')
---------------------------------------------------------------------
34456.789
SQL> select to_char(sysdate, 'fm"Today''s" ddth Month YYYY') from dual;
TO_CHAR(SYSDATE,'FM"TODAY''S"DDTHMONTHYYYY')
------------------------------------------------------
Today's 16th August 2013
extract the value of the strings for REQUEST_GROUP_CODE and REQUEST_GROUP_APPL_SHORT_NAME. As you can see I have to deal with whitespace differences, case differences etc. I need the values between the quotes for each one. get ACCOUNTING and AR for the first example. I am using db version 11g r1.
WITH TEST AS
(SELECT 'REQUEST_GROUP_CODE="ACCOUNTING" REQUEST_GROUP_APPL_SHORT_NAME="AR" TITLE="AR:AR_SRS_TITLE_ACC_RPTS"' str FROM dual
UNION ALL
SELECT 'REQUEST_GROUP_CODE = "PRINT_CONSINV"REQUEST_GROUP_APPL_SHORT_NAME = "AR"TITLE =
[code]...
splitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
I have to compare a string entered in a form with a series of English strings in back-end PL/SQL( using LIKE operator )
But it so happens, the string entered in the form is specific to the language used in the country. for ex. in Dutch, it is entered in Dutch language.
So on comparing, it fails as the PL/SQL compares it with English Strings.
Outside of convoluted loop using the SUBSTR() function, is there an easy way to extract each element from a comma-sepearted list that's passed in to a stored proc?
View 4 Replies View RelatedI am importing some data using an external table, but the file on which the external table is built has some rows where a certain column is populated with two empty space characters.
CREATE OR REPLACE DIRECTORY xtern_data_dir AS 'C:/...';
CREATE TABLE ET_RPDMMA1_PEDI_MSTR (
GCN_SEQNO NUMBER(6),
PDM_MNAGE NUMBER(4),
PDM_MXAGE NUMBER(4),
PDM_MND NUMBER(18,6),
[code].......
This is an except of what's in the external text file. The full text file has been attached.
000011|0030|....|000000.000000| |000000.000000| |0002
000011|0365|....|000000.000000| |000000.000000| |0002
000011|0730|....|000000.000000| |000000.000000| |0002
^
blank spaces may be |
causing error----------
Here is the error message I am receiving. I believe this is caused by the blank fields in the data.
INSERT INTO RPDMMA1_PEDI_MSTR (GCN_SEQNO,....
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at "SYS.ORACLE_LOADER", line 52
This is difficult to work with because the external table function does not appear to be even reading the file so it's not like I can convert the data as I'm loading into the internal database table. What are some approaches I can use to get Oracle to accept these blank columns and either populate them with blank spaces or set them to null?
tyring to insert an insery query having string as a column where i am supposed to insert a single quote casuing the problem.
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh='self' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
getting missing comma with the above query.when i tried to give as
insert into abc(x,y) values (1,'select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij')
insert is happening but saving as "select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij"
how to avoid this and get the select query to store as
select abc,bbc from T_AB A,select fgh,hij from T_AB where fgh=''self'' group by fgh,hij having count(fgh)>1) B) where A.hij=B.hij