I have a column in a table that contains a string seperated by .
e.g.
IT.HARDWARE
IT.APPS
IT.SOFTWARE
I would like to split the two values out on two columns e.g.
Column1 - IT
Column2 - Hardware
etc.
I have strings like
1) ICE_10001 ICE_10002 ICE_10003
2) ICE_10005 ICE_10006
i want to split above strings like
1)ICE_10001
2)ICE_10002
3)ICE_10003
4)ICE_10005
5)ICE_10006
If it is possible in oracle sql
I have a string like '9999999;A' one field as numeric & other as char.Now i want to split this string into two values removing the ; delimiter in oracle using for loop.
View 2 Replies View Relatedi have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!
SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;
but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..
how I can change this function to pass argument that length is 32676 .
create or replace TYPE "HRS_SPLIT_TBL_T" as table of varchar2(32767);
create or replace function hrs_split
(
p_list varchar2,
p_del varchar2 := ','
) return hrs_split_tbl_t pipelined
is
[Code]...
I got error String literal too long when execute select below
select distinct COLUMN_VALUE Tbat_latn
from table(
HRS_SPLIT( 'PER0000002,PER0000094,PER0000094,PER0000096,PER0000096,
PER0000024,PER0000024,SAB0000001,SAB0000001,PER0000002,
PER0000096,PER0000094,PER0000094,PER0000002,PER0000024,
PER0000024,PER0000096,PER0000096,PER0000094,PER0000094,
PER0000002,PER0000024,PER0000024,PER0000096,PER0000096,
PER0000094,PER0000094,PER0000002,PER0000024,PER0000024,
[Code]....
I want to split any user given string into two parts, how can I do it.
suppose some sample inputs may be:
'123456' splits into '123','456' ,'123456789' splits into '12345','6789'
NOTE: the first half splitted string length is greater in case of odd no. string as in second example
How can I do it?
I have a string like 10.01.03.04.234 or 234.05.07.032.� Is there a simple function in Oracle that would give me
10
01
03
04
234 as seperate values in a select clause.
I would like to do a
Select function(10.01.03.04.234)
into var1, var2, var3, var4, var5
from ....
I can accomplish the same with substr/length/instrs in a procedure...but looking for a simple function, if any, instead of writing a proc/function....
How do i split a string based on a space. And then create a view with three columns of that split string.
For example if i have a table with Full name as a column how do i split that string and create a view with firstname, middlename, lastname as column names with the split string as data.
If you need anything clarifying write back.
Split a column with values like 1-2-21-3 into 001-002-21-003 (ie format required is 000-000-00-000) using sql.
View 3 Replies View RelatedI 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 am using this query to split numeric values i.e ('1,2,3,4,5,6')but when i am trying with char value i.e ('a,b,c,d'), its not working.
select id
FROM employee e
WHERE e.id IN
( SELECT TO_NUMBER(xt.column_value)
FROM XMLTABLE('1,2,3,4,5,6') xt );
I have created a function that is used for splitting a comma separated string & give the output in tabular form.here is the function
Here I have used CLOB as my input string will be huge(greater than max limit of varchar2)
CREATE OR REPLACE TYPE SPLIT_TBL_CLOB AS TABLE OF CLOB;
CREATE OR REPLACE FUNCTION CSVTOSTRING_CLOB
(
P_LIST CLOB,
P_DEL VARCHAR2 := ','
) RETURN SPLIT_TBL_CLOB PIPELINED
[code]....
But here I am facing 2 problems.
1. The function is not accepting a large string & I am getting the error
ORA-01704: string literal too long
2. The function is going for an infinite loop.
notes column having 2000 characters max, i want my string output based on 35 characters, ya i need to replace tag after 30 characters in the string.. I need out put as "hi hello how are you doing out there, similar i need to calculate the sting length and have to split it 35+35+35..
This i tried
select substr(note,1,(instr(note, ' ',35)))||'
'||substr(note,instr(note, ' ',35),(instr(note, ' ',35)))notes from test
How to split the values from a column?
For example: i had table T1 with below structue
Table T1
userid
-------
sandy1234
raj6785
Andrew12367
Michael56098
i need output like below structure
Nameid
---- ----
Sandy1234
Raj6785
Andrew12367
Michael56098
I have a requirement in which the amount need to be split in to multiple period.
For example if there amount of 3000 and start and end date is 01-jan-2013 and 31-mar-2013 then the output of the query should be
Name Start Period End Period Jan-13 Feb-13 Mar-13
------------------------------------------------------------------------------
XXX 01-JAN-13 31-MAR-13 1000 1000 1000
How to achieve this.
I have a table with following values in a column
Table A
col1
10
35
20
25
I need to form a query which will take these four values in rownum part and split the records into 4 groups in Table B.
Table B- 90 records (10 + 35 + 20 + 25)
Now for example, the Table B is having emp no, order by ascending and i need to split into 4 groups,
with first group having start value -1 and end value -10
second group - start value -11 and end value-45
third group - start value -46 and end value -65
fourth group - start value - 66 and end value-90
one way i can do it by using union and count, which was a bit tedious if the no. of group goes upto 10.
note that the values in Table A is dynamically changing, so not able to hard code values.
I have data something like this:
Sample DateWITH DATA AS ( SELECT 'AAAXXXX IO BLUEEXPRESS' LIST FROM dual UNION ALL SELECT 'BLUEEXPRESS AAAXXXX IO BLUEEXPRESS' FROM dual UNION ALL SELECT 'DDDDD BLUEEXPRESS AAAXXXX' FROM dual UNION ALL SELECT 'DDDDD DDDDD AAAXXXX' FROM dual UNION ALL SELECT 'DDDDD BLUEEXPRESS AAAXXXX NO CARBON' FROM dual UNION ALL SELECT 'NO CARBON
[code]....
The above result depends on the following rules:
- Replace BLUEEXPRESS into BEXPRESS
- Remove the term NO CARBON (See row no 6)
- Reduce all multiple space into single space (see last record).
So far I create separated queries for replacing BLUEEXPRESS into BEXPRESS and replace NOCARBON term but I don't know how to do it in a single shot as well as stuck on scenario to remove multiple spaces and put single space.
Objective: I need to compile a final string by concatinating the unique values from different strings.
Here is the script to create tables and data.
Create table temp_acronyms(id number, acronym varchar2(30);
insert into temp_acronyms values(1, 'ABC');
insert into temp_acronyms values(2, 'DEC//NOFO');
insert into temp_acronyms values(3, 'CBK//FO TO USA');
insert into temp_acronyms values(4, 'DEC//NO ENTRY');
insert into temp_acronyms values(5, 'ABC//NOFO');
COMMIT;
select * from temp_acronyms;
ID ACRONYM
--- --------
1 ABC
2 DEC//NOFO
3 CBK//FO TO USA
4 DEC//NO ENTRY
5 ABC//NOFO
I need to store all the unique strings from the acronyms for id's 1,2, 3, 4 and 5 into a variable. doesn't matter even if it is through database procedure.
my final string should have the values as below
ABC//DEC//NOFO//CBK//FO TO USA//NO ENTRY
I am trying to update the greatest value in a column from a string of other column.
Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only
so for that I had written query as follows:
update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))
The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.
CREATE TABLE TEST_TAB
(
A NUMBER(5),
B VARCHAR2(20)
) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Manoj' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Arun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Varun' ) ;
INSERT INTO TEST_TAB VALUES ( 1, 'Suresh' ) ;
[code].....
Query Output :
1Arun,Arun,Manoj,Manoj,Manoj,Suresh,Varun
2Kamlesh,Manoj,Manoj,Manoj,Suresh,Suresh
Expected Output :
1Arun,Manoj,Suresh,Varun
2Kamlesh,Manoj,Suresh
Expectation here is duplicate values should not be repeated.
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
"CORE 11.1.0.6.0 Production"
I have a cursor, whose sql is returning seven rows with these values:
9
4
4
9
7
9
4
i open cursor and fetch these values into variable as shown
OPEN id_search FOR l_sql_stmt;
LOOP
FETCH id_search INTO l_eve_id;
if l_eve_id != l_eve_id_prev then
l_eve_id_str := l_eve_id_str || ' , ' ||l_eve_id ;
[code].......
but i want only the distinct values in l_eve_id_str , i.e.,
l_eve_id_str := 9,4,7
What this code is doing is creating a string with all the values
l_eve_id_str := 9,4,4,9,7,9,4
How to remove duplicates from this string?
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 ...
My DB version is 10.1.0.5.0
I want extract the values from the string using below query but i am unable to bring the correct result.
WITH t AS ( select '123,1,3,22' col FROM DUAL
UNION ALL
SELECT '123,,2,1' FROM DUAL
UNION ALL
SELECT '5,1,2,,' FROM DUAL
)
[Code]...
My required result like below.
123 1 3 22
123 2 1
5 1 2
get required result using regular expressions.
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 table(PSUSEROBJTYPE) with a long field(PTCUSTFORMAT) containing a row value value in the form:
#1|0|0|0|0|#2|1|0|0|1|#3|1|0|0|0|#4|0|0|0|0
Here, I want to update the above field value to a value in the form:
#2|0|0|0|0|#3|1|0|0|1|#4|1|0|0|0|#5|0|0|0|0
This is nothing but finding each occurrence of (#n) in the above string and replacing it by (#n+1). (i.e #1 is replaced by #2,#2 is replaced by #3).
I have a problem with Between clause used in where statement to compare two string variable.
Query is like this,
select item_code, item_deacrption
from itm_master, invoce_det
where im_code = item_code
AND invd_item_number BETWEEN (:startNum) AND (:endNum)
Here invd_item_number is a DB field and is of type varchar2(41), and (:startNum),(:endNum) are of same type.
now invd_item_number has one value '001003002001'
if we give :startNum = '001003001002' and :endNum = '001003004006'
:startNum and :endNum is composed of separate field values (ie, 1st 3 character shows color code, next 3 for catagory, next 3 for size etc). These codes are entered separately and are combined at run time.
it is still fetching the invd_item_number with value '001003002001'. (the last set of character(type code) in the :startNum is greater than invd_item_number's type code value. But it is smaller than the previous code (size code), that's why it is fetching).
But how can i get around this as i don't need that value to be fetched.
I'm trying to figure out how to process a string of values that is being passed from Cold Fusion.
procedure test (Names in varchar2) is The Names variable from the Cold Fusion page would have values like Joe1,Joe2,Joe3,Joe4.
I need to loop through the Names variable (Joe1 then Joe2 then Joe3 and so on) and insert each one into a table. how to do that within the procedure?
I am using the below code to update specific sub-partition data using oracle merge statements.
I am getting the sub-partition name and passing this as a string to the sub-partition clause.
The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.
We are using Oracle 11gr2 database.
Below is the code which I am using to populate the data.
declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin
[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?
Database version: 11.2.0.3.0
I need to remove duplicate values from concatenated long string of state codes(comma separated). Ex: 'VA,VA,PA,PA,CT,NJ,CT,VA'. I tried following query and did not get required out put.
select regexp_replace('VA,VA,PA,PA,CT,NJ,CT,VA,CT,PA,VA,CT','([^,]*)(,1)+($|,)', '13') new_str from dual;
Define Meta-character's format in regular expression to get desired result. Out put required: VA,PA,CT,NJ (with out any duplicates).