I have a comma separated list 'black','red','white' and I want to get each of words in this list in one rows. Some time ago I done it with:
QUOTE select * from XXX('black','red','white')
where XXX was a function which converted list to table.
I have a string like this .
'ABC-XYZ-MNO'
and i want the data in the below format
'ABC','XYZ','MNO'
I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
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
Here is what i have in ms-sql, how to convert this into t-sql ?
@MortgagePurposeID is parameter with comma seperated values ('1,2,3,4')
if(substring(@MortgagePurposeID, LEN(@MortgagePurposeID)-1,1)<>'','')
Set @MortgagePurposeID = @MortgagePurposeID + '',''
Set @pos=0
[Code].....
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 have a few questions about querying using ranges and comma separated lists. The basic situation is a request comes in with part numbers that can be formatted in a range, comma separated lists or both. For an example, the request contains the following part numbers:
<pnum> 1-10, 14, 17, 11, 21-24 </pnum>
I can muster a basic SQL statement to query for this by hand (more then one way to do this)-
SELECT *
FROM part_table
WHERE pnum BETWEEN '1' AND '10'
OR pnum BETWEEN '21' AND '24'
OR pnum IN (14, 17, 11);
is there a way to create the BETWEEN statement so that the dash doesnt need to be parsed out of the request? (like BETWEEN '1-10') or something that functions to that extent? Is it also possible to nest the BETWEEN statements (or the functionality of the BETWEEN) in the IN statement?
We have a existing view which pulls list of orders against a deal. Straight forward result of the view is below.
Deal idSALES_ORDER_NUMBER
280760548460578
280760548789518
280760548794798
280760548794799
280760548873291
280760548887725
280760548900581
Each deal may have different number of orders.
We need to modify the view logic to result the list as comma seperated:
48460578,48789518,48794798,48794799,48873291,48887725,48900581
how to proceed.
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 would like get rows into comma separated values expected output
rowvalue1,<space>rowvalue2,<space>rowvalue3,<space>rowvalue4,.....Example:
create table test1 (name1 varchar2(10));
insert into test1 values ('JOHN');
insert into test1 values ('YING');
insert into test1 values ('KAREN');
insert into test1 values ('PEDRO');
commit;
SQL> select * from test1;
NAME1
----------
JOHN
YING
KAREN
PEDROHow can I get this to printed as
JOHN, YING, KAREN, PEDRO
SELECT 'TEST','F1,F2,F3,F4' from dual
I want to split the rows by comma separated as below
TEST F1
TEST F2
TEST F3
TEST F4
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 have a requirement wherein I need to enforce certain column to have only comma as a delimiter, where multiple values exists.
Example:
Create table:
create table test_oz ( slot number, server_name varchar2(50), used_by varchar2(50),
constraint test_oz_pk primary key (slot, server_name) );
Insert Test Data:
insert into test_oz values ( 1,'SRV1','SAMMY' );
insert into test_oz values ( 2,'SRV1','SAMMY,TOM' );
commit;
Data:
SQL> select * from test_oz;
SLOT SERVER_NAM USED_BY
---------- ---------- ----------
1 SRV1 SAMMY
2 SRV1 SAMMY,TOM
From above, the USED_BY column data need to be only comma separated. Is there a way to enforce that?
When user tries to insert data using any other delimiter, it should fail.
I was trying to see if a CHECK CONSTRAINT could be of use, but could not find it to work.
I am building a search for use in one of our major applications. I have written a PL/SQL package that deals with it. I would like to present the requirement list to the group and see what, if anything, you may have done differently than I have.
1.) The search interface must have a single box, like google.
2.) Multiple search terms will be separated by a comma.
3.) The table has the following columns:
-- Name
-- Title
-- addr
-- addr2
-- city
-- state
-- zip
-- phone
-- email
4.) Number of Search Terms per query will be unlimited. (for now, as practicality dictates)
5.) Each search term will be checked against various columns.
6.) Search terms must not have a preference in order. Name, Address = Address, Name
7.) Records will be returned only for the rows where all search terms are found.
I need to write a function which will take table name as input and should return all the columns separated by coma (,).
For example I have a table product as
PROD_ID PROD_NAME FAMILY_ID
------------------------------------
100006Acetaminophen100005
100013Simvastatin100007
100014Ezetimibe100008
100015Simvastatin+Ezetimibe Oral Family100009
100003Abacavir100003
100007Amlodipine100006
100001Cetirizine HCl Oral Solution100001
My function should return the output as
100006,Acetaminophen,100005
100013,Simvastatin,100007
100014,Ezetimibe,100008
100015,Simvastatin+Ezetimibe Oral Family,100009
100003,Abacavir,100003
100007,Amlodipine,100006
100001,Cetirizine HCl Oral Solution,100001
Is there any inbuilt function available?
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 the following query : for :P_LEG_NUM Parameter when i am passing values like 1,2,5 as string type i am getting invalid number error... I have defined in clause for it but still it does not work.. For individual values like 2, etc it works... how can i pass comma separated values for this bind variable
select trip_number as prl_trip_number,
flight_number as prl_f_number,
trip_leg_id as prl_trip_leg_id,
leg_number as prl_leg_num,
dicao as prl_dicao,
[code]........
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..
how can I convert
select 1 as id, 'role1,role2,role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
to
select 1 as id, 'role1' as roles from dual union all
select 1 as id, 'role2' as roles from dual union all
select 1 as id, 'role3' as roles from dual union all
select 2 as id, 'role1' as roles from dual
?
I would prefer sql then plsql. Script for creating a test table:
create table CONVERT_LIST(id integer, roles varchar2(100));
insert into CONVERT_LIST values(1,'role1,role2,role3');
insert into CONVERT_LIST values(2,'role1');
I have a table with 9 regions. How can I export them in 9 seperated dump file?
View 4 Replies View RelatedI am working on a POC. The goal is to do a
select * from function('input')
where the function dynamically returns different tables.
I have gotten to the point where I can return the cursor but I cannot format it as a table to use in the query. It return's it as XML. This is my function:
create or replace
FUNCTION CAMS_FUN_GEN_REPORTS(PARAM1 IN VARCHAR2) RETURN SYS_refCURSOR AS
BEGIN
DECLARE
p_recordset SYS_refCURSOR;
begin
OPEN p_recordset FOR 'SELECT * from STATS_FLAGGED_TOTALS_ME';
RETURN p_recordset;
END;
END CAMS_FUN_GEN_REPORTS;
This is my query:
select * from CAMS_FUN_GEN_REPORTS('')
So normally you would do this
select * from TABLE(CAMS_FUN_GEN_REPORTS('') as tabletype)
But I will never know the end result table as it will be dynamic so I have to find a way to cast the result of the function into a new table I can select on.
I have looked into pipe lining also but from what I can tell you still need to know the table definition. Which I can't know from the client side. The server will control the 'routing' and thus the end result of the select.
How we can escape comma while exporting data from table into csv file.
CREATE TABLE emp
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
address varchar2(100),
[code].......
i have to export data from emp table which has address column and address column contain comma, when i am running below script, the comma part in address field comes in next tab in csv file, is there any way we can avoid shifting to next tab and can have complete address in one tab.
set echo off
set verify off
set termout on
set heading off
set pages 50000
[code]....
I have a table A, whose table structure is in the below format.
Table A
ID DESC VALUE
123 A 454
123 B 1111
123 C 111
123 D 222
124 A 123
124 B 1
124 C 111
124 D 44
Now i need to insert the data from this table to another table B, the sturcture of which is as below
Table B
ID A B C D
1234541111111222
124123111144
How do i frame a query to fetch data from table A and insert that into table B? I don't want to use max and decode combination. as it would return only single row for an ID. I need all the id's to be displayed.
To remove the last comma end of string and load the Clob data into table. create table test(name clob)
View 2 Replies View RelatedI am more of a C/C++ guy and relatively amateur in oracle. I have to update a table field from "Long" to "CLOB". I have planned to do a simple alter table, and as far as I know there won't be any issues.
Queries:
1. Although I have triple checked, is there any scenario under which there can be any data loss during the data type change? The data is very critical and no data loss can be entertained.
2. Is there any easy way to update all the related views without having to do so manually?
3. Any particular precautions I should take before introducing the change?
how to convert a record type to a table type?
View 4 Replies View RelatedEnter 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.
Is possible to list all users that have any privilege on a specific table?
View 2 Replies View Related