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.
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
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?
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?
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
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 || ',';
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]........
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..
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.
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;
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
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.
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?
