SQL & PL/SQL :: Multiple Search Terms Will Be Separated By A Comma

Dec 12, 2011

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.

View 3 Replies

SQL & PL/SQL :: Amend Hyphen Separated String To Comma Separated

Dec 12, 2010

I have a string like this .

'ABC-XYZ-MNO'

and i want the data in the below format

'ABC','XYZ','MNO'

View 14 Replies View Related

SQL & PL/SQL :: Get Comma Separated Values

Oct 19, 2012

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

View 6 Replies View Related

Comma Separated Values In Parameter?

Jul 2, 2008

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].....

View 2 Replies View Related

Parsing Comma-separated String?

Jan 22, 2009

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.

View 1 Replies View Related

SQL & PL/SQL :: Range And Comma Separated Queries

May 30, 2013

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?

View 1 Replies View Related

SQL & PL/SQL :: Comma Separated View Results

Jun 19, 2010

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.

View 2 Replies View Related

SQL & PL/SQL :: Processing Comma Separated Strings

Mar 3, 2003

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 Related

PL/SQL :: Rows Into Comma Separated Values

Oct 30, 2012

I 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

View 3 Replies View Related

SQL & PL/SQL :: To Split The Rows By Comma Separated

Mar 4, 2011

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

View 5 Replies View Related

PL/SQL :: Split Comma Separated String

Mar 11, 2013

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

View 3 Replies View Related

PL/SQL :: Comma Separated String In Rows

Oct 23, 2013

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);

View 4 Replies View Related

SQL & PL/SQL :: Sort A Comma Separated String?

Aug 4, 2008

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]...

View 26 Replies View Related

Converting Comma Separated List To Table

Dec 7, 2011

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.

View 1 Replies View Related

SQL & PL/SQL :: Enforce Comma Separated Column Values?

May 21, 2012

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.

View 3 Replies View Related

SQL & PL/SQL :: Function To Select Column Values Separated By Comma?

May 23, 2012

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?

View 10 Replies View Related

SQL & PL/SQL :: Count Number Of Words In A String Separated By Comma

Aug 31, 2011

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

View 9 Replies View Related

PL/SQL :: Unable To Pass Comma Separated Values For In Clause

Apr 24, 2013

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]........

View 2 Replies View Related

Performance Tuning :: Split Data Separated By Comma / Then Create Collection With Data Mapped From Other Columns

Sep 25, 2013

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..

View 4 Replies View Related

SQL & PL/SQL :: Separated Result With Multiple Values

Aug 24, 2010

with t as
( select 1 id, 101 book_id, 'MICROBIOLOGY' book_type, 1 category, 'sCIENCE AND TECH' category_name
from dual
union all
select 1 , 101 , 'MICROBIOLOGY', 2 , 'HEALTHCARE' from dual
union all

[Code]....

id book_id BOOK_TYPE category category_name

1 101 MICROBIOLOGY 1 SCIENCE AND TECH
1 101 MICROBIOLOGY 2 HEALTHCARE
1 102 CHEMISTRY 5 CHEMICAL ENGINEERING
2 105 COMP SC 1 SCIENCE AND TECH

The above is the output for a query after joining multiple tables. I have just put here the output I am getting after joining the tables.Now I want to achieve the below result.

Expected output: it should be | delimited

1|101|MICROBIOLOGY|102|CHEMISTRY|1|sCIENCE AND TECH|2|HEALTHCARE
2|105|COMP SC|1|SCIENCE AND TECH

Is there any alternative way other than SYS_CONNECT_BY_PATH? I also tried to use CONCAT_ALL but its not working.

View 13 Replies View Related

SQL & PL/SQL :: Use Model Clause To Get Comma Separate Single Row For Multiple Rows?

Feb 19, 2010

I am trying to use model clause to get comma separate single row for multiple rows. My scenario is like this:

SQL> desc test1
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NUMBER
VALUE CHAR(6)

SQL> select * from test1 order by id;

ID VALUE
---------- ------
1 Value1
2 Value2
3 Value3
4 Value4
5 Value4
6
7 value5
8

The query that I have is:
SQL> with t as
2 ( select distinct substr(value,2) value
3 from test1
4 model
5 ignore nav
6 dimension by (id)
7 measures (cast(value as varchar2(100)) value)
8 rules
9 ( value[any] order by id = value[cv()-1] || ',' || value[cv()]
10 )
11 )
12 select max(value) oneline
13 from t;

ONELINE
---------------------------------------------------------------------------------------------------
Value1,Value2,Value3,Value4,Value4,,value5,

what I want is : null value should not come and duplicate value should not come (Value4 in output above)

View 11 Replies View Related

SQL & PL/SQL :: Obtain Top 2 Departments In Terms Of Total Salary?

Aug 21, 2010

I need to obtain the top 2 departments in terms of total salary.

I have the following tables (Dno corresponds to DeptNo). I've found tutorials for obtaining the salaries of the top three employees, and summing the salaries for each dept. is easy, but I can't seem to combine the two.

DNo Fname Salary
2 Tom 10000
3 Mike 20000
2 Harry 30000

DeptNo DeptName
1 Administration
2 Special
3 Finance

View 6 Replies View Related

Tablespace Query - Output In Terms Of Maxsize And Not Size Allocated

Oct 11, 2012

Tablespace freespace query which will give output in terms of maxsize and not size allocated ?

View 14 Replies View Related

Application Express :: Enable IR Search Field To Search In Hidden Columns

Jul 15, 2012

I am using apex 4.1. I must hide phone number columns in my IR report, but at the same time the values of that columns should be available to search for using IR Search Field. is there a way to do this ?

if not, that means I have to :

1- Add a text filed P1_PHONE
2- edit my report query to something similar to
> Select * from Table where :P1_phone in (mobile1,mobile2) or :p1_phone is null
3- add button to refresh the report.

but the item P1_PHONE should be on the header of the Report region. is there a way to do this.
I am using theme 23
page template without sidebars
Report template : Reports Region.

how to put the item P1_PHONE on the tab of the page. Just similar to the Search item of in the Application Builder.

View 7 Replies View Related

SQL & PL/SQL :: Data With Semicolon Separated

Sep 25, 2012

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.

View 6 Replies View Related

SQL & PL/SQL :: How To Get Concatenated Values Separated In Rows

Mar 1, 2012

I have input like below

ID | Name
--------------
1 | ABC, BCA, AAA, BBB (all in one column)
2 | ABC,DBA

and I want to get concatenated values separated

ID | Name
--------------
1 | ABC
1 | BCA
1 | AAA
1 | BBB
2 | ABC
2 | DBA

View 17 Replies View Related

SQL & PL/SQL :: Display Values Separated By Commas

Jun 22, 2010

I am posting the table and data, i just want to display the columns values separated by ','.

SQL> create table t(c1 varchar2(10),
2 c2 varchar2(10),
3 c3 varchar2(10),
4 c4 varchar2(10));

Table created.

SQL> insert into t values ('A','B','C',null);

1 row created.

SQL> insert into t values ('A','B',null,'D');
1 row created.
SQL> insert into t values ('A',null,'C','D');
1 row created.
[code]....

But it giving extra comma in 1,5,8 rows.

View 3 Replies View Related

Insert Separated Data As Individual Rows

Sep 3, 2008

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?

View 3 Replies View Related

PL/SQL :: Extracting Data In A String Separated In Brackets

Jun 7, 2012

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.