SQL & PL/SQL :: String Matching?

Feb 22, 2011

check out following scripts

CREATE TABLE email (
ids VARCHAR2(500))
INSERT INTO email VALUES('1233@gmail.com;3456@gmail.com;0954@gmail.com')
INSERT INTO email VALUES('7658@gmail.com;346@gmail.com;6346@gmail.com')
CREATE OR REPLACE FUNCTION is_same(emailid VARCHAR2) RETURN BOOLEAN

[code]...

how to write --emailid is present in rec.ids THEN this logic in function

View 2 Replies


ADVERTISEMENT

SQL & PL/SQL :: Get Exact Matching String

Dec 11, 2012

When I pass the input as 'micky', then i should get the count of records as 4. I tried to use REGEXP_LIKE, but could not get the result.

Test Case:

DROP TABLE test1;
CREATE TABLE test1( pattern_series VARCHAR2(30));
INSERT INTO test1 VALUES ('qa_micky1');
INSERT INTO test1 VALUES ('qa_micky2');
[code].....

-- I should get the output as 4 not 8

View 5 Replies View Related

SQL & PL/SQL :: Insert Using String Matches / Matching And Searching Match

Jul 3, 2012

i have three tables ot_cut_head,ot_cut_det and om_mc_master based on which fourth table ot_cut_opr and fifth table ot_cut_mc must get populated , Conditions are as follows

first one is based on job_no in ot_cut_head the selection criteria will be filtered,if the job number is like '%M' then type MISC will be chosen ,if job number is '%G' then GRAT TYPE will be picked from om_mc_master (Machine Master) and operations and machines based on this will be filtered.

Second all the cd_ps_desc will be taken from ot_cut_det and will be compared with om_mc_master to get their corresponding operation codes and machine codes , there can be 2 operations or 1 operation.

Finally if the match is found record will be inserted into ot_cut_opr and ot_cut_mc ,based on the criterias and what i want is the search criteria to be more flexible and if there are 2 operations 2 rows will be inserted and if one opeation is defined in om_mc_master ,then only one record will be inserted.

We have to make sure that if based on operation number stage will be populated ,if its first operation then stage will be 1 and if its second operation the stage will be 2.like previous operation also depends on them , the second operation will have the previous operation as first operation and so on.

CREATE TABLE om_mc_master ( mc_type VARCHAR2(12),mc_prof VARCHAR2(30),mc_prep_cd1 VARCHAR2(30),mc_mach_cd1 VARCHAR2
(30),mc_prep_cd2 VARCHAR2(30),mc_mach_cd2 VARCHAR2(30));
INSERT INTO OM_MC_MASTER VALUES ('MISC','TEE SCH','IR','HO','RE','HO');
insert into om_mc_master values('MISC','Vertical Brace','R','HM','I','HO');
insert into om_mc_master values('MISC','Pipe','IR','HO',NULL,NULL);
INSERT INTO OM_MC_MASTER VALUES ('GRAT','PL','RE','HO',NULL,NULL);
SQL> SELECT * FROM OM_MC_MASTER;
[code]....

View 6 Replies View Related

SQL & PL/SQL :: Pattern Matching And Updating Of Number Values In String?

Nov 5, 2011

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

View 4 Replies View Related

SQL & PL/SQL :: Matching Query

May 15, 2012

i have two table menu_user_d and emp_master , the requirement is i need to match names from both these tables like i want want to match menu_user_d.user_desc to emp_master.emp_name to get the emp_id and update the correct emp_id into menu_user_d.user_emp_id , i have prepared a test case and wrote one query but could'nt succeed in getting all the matching names, is there a way i can do this correct matching and then update it automatically.

SQL> create table menu_user_d (user_id varchar2(12),user_desc varchar2(30),user_emp_id varchar2(12));

Table created.

SQL> insert all
2 into menu_user_d(user_id,user_desc,user_emp_id) values ('ARIF','Mohammed Arif',null)
3 into menu_user_d(user_id,user_desc,user_emp_id) values ('wajahat','Wajahat',null)
4 into menu_user_d(user_id,user_desc,user_emp_id) values ('Imad','Imd',null)
5 select * from dual;

3 rows created.

SQL> commit;

Commit complete.

SQL> create table emp_master (emp_id varchar2(12),emp_name varchar2(30));

Table created.

SQL> insert all
2 into emp_master(emp_id,emp_name) values ('7014','Md.Arif')
3 into emp_master(emp_id,emp_name) values ('6777','Wajahat')
4 into emp_master(emp_id,emp_name) values ('1008','Imad El Kane')
5 select * from dual;

3 rows created.

SQL> commit;

Commit complete.

SQL> SELECT USER_ID,USER_DESC,USER_emp_id,EMP_id,EMP_NAME
2 FROM MENU_USER_d,EMP_master
3 WHERE UPPER(USER_DESC) LIKE UPPER(EMP_NAME) ;

USER_ID USER_DESC USER_EMP_ID EMP_ID EMP_NAME
------------ ------------------------------ ------------ ------------ ------------------------------
wajahat Wajahat 6777 Wajahat

View 28 Replies View Related

Any 200 Matching Records From A Table?

Aug 10, 2011

I have a personname table which contains records of millions Person-names. My application has a requirment to return "any" 200 names that match the given Firstname and lastname entered by user.note the NOT actually "top-n", but "Any-N" , i.e. user wants "any" 200 names and NOT in any "specific order".

which is the best option to make most efficient search --

1) rownum < 201
2) row_num()
3) rank/denserank etc

View 3 Replies View Related

SQL & PL/SQL :: Group By Matching Records

Dec 19, 2012

I have one table employee where there are 4 fields ,emp_code,emp_locn,emp_job_code,emp_job_desc the problem is i am trying to prepare a group reports based on location and emp_job_code there is a duplication of data in the emp_job_desc ,

For example there is a job_code E2 Which has two different job_descriptions for two different employees like E2-PAINTER-SPRAY, E2- PAINTER -SPRAY, another example is E1-rigger , E2-RIGGER and so on.Is there a method to match them together as one description.

View 7 Replies View Related

SQL & PL/SQL :: How To Find Two Words Matching

Aug 19, 2013

i am in need to find starting two words matching like i have string "my name is person". i want starting two words find "my name". i tried with this

SELECT T.FULL_NAME,SUBSTR(t.full_name,0,INSTR(t.full_name, ' ')-1+INSTR(t.full_name, ' ')-1) AS outpu2
FROM test t

output: it's giving me the out put but not in proper way som where the second word is cut off and some where first word is not coming.

View 6 Replies View Related

SQL & PL/SQL :: Pattern Matching Utility

Jul 2, 2013

I am using 11.2.0.3.0 version of oracle. I have not worked on regular expressions. During working on sql injection, I got set of below patterns which is feeded to some JAVA regx classes or utilityto restrict selective Request, based on patterns.below patterns and the characters which will be restricted by this pattern matching utility.

Given below are 3- patterns:

(.*?[sd)'])(?:AND|OR)(?=[s-+(']|.?d)(s*[-+(]?s*(?:[^s!<>=]+?|'.*?')[s)]*(?:s*(?:(?:[-+/*(.]||s*|)s*)+(?:[^s!<>=]+?|'.*?')[s)]*)*s*)(?: <s*>|>s*=|<s*=|!s*=|=|>|<)(.*)
(.*?[sd)'])(?:AND|OR)(?=[s-+(']|.?d)(s*[-+(]?s*(?:[^s]+?|'.*?')[s)]*(?:s*(?:(?:[-+/*(.]||s*|)s*)+(?:[^s]+?|'.*?')[ s)]*)*s*)(? <=[s)']|d.?)(?:LIKE|IN|BETWEEN)([s-+('].*)
.*[sd)'](?:AND|OR)[(+-s]*(?:'.*?'|.?d[ds-+/*().]*)[)s]*(?:<s*>|>s*=|<s*=|!s*=|=|>|<|LIKE|IN|BETWEEN)[(+-s]*(?:(+s*SELECT)?[(+-s]*(?:'.*?'|.?d[ds-+/*().]*).*

View 1 Replies View Related

SQL & PL/SQL :: Matching Dates Using Dual?

Jul 7, 2010

What I'm trying to do is make it so that it returns 'MATCH' when I pass a date that matches a date every two weeks starting Jan 01. Like Jan 01, Jan 15, Jan 29, Feb 12, etc. would return as MATCH Jan 02, Jan 03, etc. would return as NO_MATCH

The part in bold is what I'm having trouble figuring out.

select nvl(
(select 'MATCH'
from dual
where 'date' = '2 week intervals starting Jan 01'
), 'NO_MATCH') from dual

View 7 Replies View Related

PL/SQL :: Get A Return Value Of 0 Whenever There Is No Matching Record?

Mar 27, 2013

In PL/SQL, the following sql causes an error when there is no record matching the search criteria:

-----
select pin_number from table1 where id = 1;
-----
error: No Data Available

Is there a way to get a return value of 0 whenever there is no matching record?

View 3 Replies View Related

SQL & PL/SQL :: How To Match Two Words Matching From Two Table

Aug 19, 2013

find two words matching from two different tables.

Example:-

table1 || table2
john Dev || Kab Leva
Zaheer khan || mark dev
Cina maater || jhon dev wood
kab leva Sumo || Tony levis

output:-

john dev || john dev
kab leva || kab leva

View 9 Replies View Related

SQL & PL/SQL :: Need To Check Pattern Matching Numbers

Apr 19, 2010

I am trying to write a pl/sql script where i need to check pattern matching numbers.My database is oracle 10g and i will put this logic in a procedure.i will pass no of tel_no to get.

if the no is 3 then i need 2072860126, 2072860127 and 2072860128(i.e all 3 in sequence)
if 2 then 2072860126, 2072860127. as such..the selected nos must be in sequence.

A query returns list od tel nos. in that tel i need to choose which staisfy my criteria.

View 1 Replies View Related

PL/SQL :: Regular Expression Matching All Given Words

Nov 8, 2012

I'm trying to match all sentences that contain words starting with given search tokens at least once. For example: if the given search token words are one and two then only sentences like "one plus one is two" should match. And should not match sentences like "one plus three is four". I was able to come up with this but I need a AND condition which I'm unable to get it right.

select count(*) from dual where regexp_like('one plus one is two', '(^|s)one|three', 'i');

Currently this gives a count of 1. But needs to give a count of 0 when the regexp is fixed.

search words : one two

one is less than two -> match
two is greater than one -> match
onetwo is union of two numbers -> match
onetwo is union of 2 numbers -> not a match as 'two' is not at the beginning of a word
one is less than three -> not a match as two is not present.

View 7 Replies View Related

Forms :: How To Use Pattern Matching In Text_IO Package

Jan 22, 2010

I have a requirement which is as follows.A file will be downloaded into a server every day at 2 A.M. The name of the file would be 'BB90170_sysdate_D'. I need to refer to this file everyday since everyday the data changes.How do i identify which is the latest file in the server folder using Forms 6i code. Means while i have tried this

'' in_file :=Text_IO.Fopen ('C:TIESPartprocurementBB90170_'||part_date,'r')". How to use pattern matching in Text_io.fopen. Part_date in refers to sysdate without Timestamp.

View 3 Replies View Related

Oracle SQL - Update Table Value By Matching Columns

Mar 15, 2010

I have two table and trying to update tableA with data from tableB but gives an error. My TableA has columns - colA,colB,colC,colX and table B has columns - colA,colB,colX. Sample data looks like this:

tableA:
XXYY, local,3/19,48
XXYY,ixc,3/19,24
XXYYlocal,3/20,48
XXYY,local,3/21,48

Table B:
XXYY,local,48
XXYY,ixc,24
PPQQ,local,72

this is the query I wrote
update tableA a
set (a.colX)=(select (b.colX) from tableB b where a.colA=b.colA and a.colB=b.colB);

When i run it gives this error:single row subquery returns more than one row.

tableB has only 1 value for colX for each colA,colB record but tableA has mutiple colA and colB repeated but for all of thsoe in table A i want to update the tableB.colX value for matching colA and colB.

View 4 Replies View Related

SQL & PL/SQL :: Displaying Record When All Columns Have Matching Records?

Jul 3, 2012

I need to display the record when all the columns have matching records,If one of them doesn't match then it should not be displayed

The following is the example

WITH t1 as
(select 159435 ky from dual)
,t3 as
(select 78 id ,'Z-' rk,'SL' cd from dual union all
select 78 id ,'Z+' rk,'SL' cd from dual union all
select 78 id ,'Z-' rk,'SL' cd from dual union all

[code].....

In the above data bg.rk= 'Z-' but one of the record in T3 is having Z+ ,So this should not be displayed (same condition with column CD) in this example cd column in both table matches I tried like above query but i'm getting the record.

View 8 Replies View Related

SQL & PL/SQL :: Unique Identifiers Matching Datatype In Oracle?

Jan 19, 2011

what is the matching datatype of unique identifier in SQL to Oracle ques regd SSIS when I map a unique identifier in SQL to Oracle via SSIS, I get curly braces at both ends in the end result.

1B66FE97-A9CA-4E0D-9593-00046E2AF7E1 - i/p(SQL Server)
{1B66FE97-A9CA-4E0D-9593-00046E2AF7E1} - end result(Oracle)

In SSIS, I convert the unique identifier to string data type.

View 1 Replies View Related

ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jan 14, 2013

I'm trying to make a foreign key against a table which has a unique index instead of a primary key.

i get this error:

Error SQL: ORA-02270: no matching unique or primary key for this column-list
02270. 00000 - "no matching unique or primary key for this column-list"
*Cause: A REFERENCES clause in a CREATE/ALTER TABLE statement gives a column-list for which there is no matching unique or primary key constraint in the referenced table.

my question is (i've searched several times with no results) can i create a foreign key with a table wich has no primary key but a unique index ?example:

CREATE TABLE TABLE1(
IDTABLE1 NUMBER(5),
NAME VARCHAR2(30)
);
CREATE TABLE TABLE2(

[code]....

or TABLE1.IDTABLE1 must be a 'Primary Key' instead of unique index ?I know it should be primary, but i need to know if it would work with the index somehow. The reason is i'm migrating a large database and the original structure in mysql uses fk with indexes and no pks in some tables (48 to be precise)

View 11 Replies View Related

Application Express :: Matching Against A Multi-select List

Mar 8, 2013

I have a 'Select List' widget (P_FILTER) which I have set to return multiple values. In my report region, I have something like this

Select A from B where B.Col_1 IN upper(:P_FILTER)

When user selects only 1 value, the report is correct but if user selects more than 1 value (e.g. 2), the report does not return any rows. How do I get the report to recognize the multiple values returned by the LOV?

View 2 Replies View Related

Create Partition without Range (data Not Matching Other Partitions)

Feb 21, 2013

I would like to cretae 3 partitions for a table

partition 1 = < 2011
partition2 = <2012
partition 3 = any other value not staisfying partition 1 and partition 2

i would like to know how to create a partition 3 without specifying the range so that the data which are not coming under the range of other partitions (Partition 1 & 2) will automatically goes to this partition 3.

Also in the above case if i insert data of year 2009 then where will it go , to partition 1 (or) 2 since both of them matches the condition (<2011 and <2012) (or) do i need to use between clause while creating partitions?

View 3 Replies View Related

PL/SQL :: Identify Matching Rows When Lead And Lag Can't Be Used Due To Multiple Similar Values

Aug 21, 2013

I am using: Oracle SQL Developer (3.0.04) Build MAin-04.34 Oracle Database 11g Enterprise Edition 11.2.0.1.0 - 64bit Production Sample dataTable

with t as (
select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',101, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 106, 105, 0, 'origin', 150, 200 from dual union all
[code]..........

 Is there a way to check in that date grouping for matching ticket_origin and ticket_destination when there may be two or more rows difference between them that does not allow me to use Lead or Lag function. Is it also possible do so without using the amount column? I also would like to identify if they are in the same area when paired (this I believe works after getting table sorted like so below then use lead lag after having the order by done) I am trying to get something like this table with results as

select to_date('8-18-2013','mm-dd-yyyy') dt, '123_' ticket_origin, '123' ticket_destination,101 startid, 101 origin, 0 destination, 'origin' objecttype, 85 amount, 100 area from dual union all
select to_date('8-18-2013','mm-dd-yyyy'), '123', '123_',0, 0, 103, 'destination', 85, 100 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124._', '124.', 105, 105, 0, 'origin', 150, 200 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '124.', '124._', 105, 0, 106, 'destination', 150, 300 from dual union all
select to_date('8-17-2013','mm-dd-yyyy'), '127_', '127', 108, 108, 0, 'origin', 50, 600 from dual union all
[code]...........

View 12 Replies View Related

Server Utilities :: ORA-02270 / No Matching Unique Or Primary Key For Column-list

Jul 6, 2010

I was cloning a schema user1 as user2 in the same database.

user1 had quota on 2 tablespaces user1_data and user1_index.

I created user with name as user2.

I created tablespace user2_data only and granted user2 unlimited quota on that tablespace only (did not grant him 'resource' role or unlimited tablespace privilege) Now exported user1 schema as follows

exp system/<passowrd> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user1_schema_exp.log owner=user1 rows=y constraints=y triggers=y indexes=y statistics=none recordlength=65535 compress=no consistent=n grants=y

then imported in in user2 schema as follows

imp system/<password> file=/u05/oradata/dump/user1_schema.dmp log=/u05/oradata/dump/user2_schema_imp.log fromuser=user1 touser=user2 rows=y constraints=y indexes=y statistics=none recordlength=65535 grants=y

during import i encountered following errors for so many constraints

"ALTER TABLE "table2" ADD CONSTRAINT "constraint_name1" FOREIGN KEY ("CTR_ID") REFERENCES "table1" ("CTR_ID") ENABLE NOVALIDATE"
IMP-00003: ORACLE error 2270 encountered
ORA-02270: no matching unique or primary key for this column-list
IMP-00017: following statement failed with ORACLE error 2270:

I found that the it happened as the primary key of table1 was not created for which error was logged in the log file

. . importing table "table1" 19441 rows imported
IMP-00015: following statement failed because the object already exists:
"ALTER TABLE "table1" ADD CONSTRAINT "T1_PK79" PRIMARY KEY ("CTR_"
"ID") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 F"
"REELISTS 1 FREELIST GROUPS 1) TABLESPACE "USER1_INDEX" LOGGING ENABLE "
. . importing table "table5" 0 rows imported

However, I checked that the T1_PK79 does not exist in the user2 schema though it exists in user1 schema Neither the index for priamry key (T1_PK79) existed in user2 schema not the table <table1> existed before this import Then what could be the reason that I am getting an error "IMP-00015: following statement failed because the object already exists"?

I assume tablespace for index would not be an issue here as other indexes got created properly in user2_index tablespace during this import.

I tried this twice, once with user2 schema and then with user3 schema as well (with different tablespace), but result is the same.

There were no users connected to the database during export and no background jobs were modiying any data in schema user1 while export.

View 4 Replies View Related

SQL & PL/SQL :: Query To Transpose Quest_id And Ans_id Columns Into Rows For Matching Records

Nov 16, 2012

create table ref_tbl (col1 varchar2(10),
col2 varchar2(10),
QUEST_ID_1 varchar2(10),
ANS_ID_1 varchar2(10),
QUEST_ID_2 varchar2(10),
ANS_ID_2 varchar2(10)
);
[code]...

Now i need to write a query to transpose the Quest_id and ans_id columns into rows for matching records...my output should be like

CUS_IDCOL1COL2QUEST_IDANS_ID
------------------------------------------------
1PMP1234Q101A1234
1PMP1234Q102A2345
1STR2345Q201A2234
2PMP1234Q101A1234
2PMP1234Q102A2345
2STR2345Q201A2234

View 11 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

ORA-01747 - Oracle Reserved Words - Query Table Matching Database With NUMBER Column?

Oct 1, 2012

I am having issue with Oracle reserved words, one of the application is using table which has NUMBER as column. I am not able to query that table matching database with NUMBER column.

HERE

select a.*
from DOC a , FOLDER B
where a.NUMBER= B.INCIDENT_ID
and b.open = 'Closed'; I tried double quotes (“”) and sigle quotes too, none of them worked.

View 12 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

I'm facing some problem even after using INSTR function in Oracle.The problem is I have written the logic in the PL/SQL block which appends all the values fetched in a loop on the basis of whether the string is present or not.

For ex:

The first value fetched from the select query first is ABCDEFG which gets appended to a variable
The next value fetched is AB even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The next value fetched is BCDEF even this has to be appended to the variable since this exactly doesn't match with ABCDEFG.
The third Value fetched is ABCDEFG this will not get appended presently according to the logic which is correct.

writing that piece of code to append the value fetched which doesn't exactly match with the existing string

View 3 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 Replies View Related

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

View 9 Replies View Related

SQL & PL/SQL :: Remove String Before And After Pipeline To Get String Between Pipeline?

May 10, 2010

I have a string like below:

string = 'HEADER||MEAL||15'

How to get 'MEAL' string? The length of the string can be various. Means, 'MEAL' can be 'INFLIGHT'. So, i cant use the substr. Is there a function that can recognize the pipeline? so that i can remove all the string before the pipeline and after the pipeline to get the string between the pipeline?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved