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
ADVERTISEMENT
Feb 4, 2012
The code which I am working on consists of an incoming dynamic string which be in the form of binary digits. The max size of the string will be 12 digits. For example, the string can be '111011000001', '000000000000', '111111011111', etc.
I need to find the number of occurences of '111' in the incoming string. Say in the 1st example, result will be 1, in the 2nd example result will be 0, and in the third example, the result will be 3.
I have been trying to capture the string length and replacing the variables '111' to find the number of occurences, but it isn't giving me the result that I want. This is what I have tried
SQL> conn hr/hr
Connected.
SQL> show user
USER is "HR"
[Code]....
I searched the forum and found a similar topic, and following that guideline, I even tried dividing the string with the length of the pattern. It works in some scenarios (the first and second examples mentioned below), while it fails in some scenarios (third example mentioned below)
SQL> select (length('11101110111') - length(replace('11101110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
3
SQL> select (length('110111110111') - length(replace('110111110111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
2
SQL> select (length('111111111111') - length(replace('111111111111','111','')))/length('111') as occurences from dual;
OCCURENCES
----------
SQL>
View 13 Replies
View Related
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
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
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
Jun 20, 2012
I want to search for a pattern and replace with a string. I can easily achieve the same in oracle 10g with REGEXP_REPLACE , I want to get the similar solution in 9i.
Eg.
I have to search for a string pattern 1234 5678 9012 6736 , I want to replace the same with XXXX XXXX XXXX XXXX.
View 8 Replies
View Related
Oct 15, 2010
I have a date column, where the date values are not stored in a specific pattern. following are the sample value from the column.
8/10/10 12:00 AM
9/22/2010 1:00AM
01/01/2001
9/1/10 6:00 PM
9/22/2009 1:00AM
i want to convert this to a standard format, 'dd/mm'yyyy'.
View 14 Replies
View Related
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
View Related
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
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
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
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
Jun 17, 2010
i want to replace 4 digit number in a given string with the same number incremented by 10000.
That mean in the given sting 1201 should be replace by 11201 (Icremented BY 10000).
Input String:
<query><matchAll>true</matchAll><row><columnId>1201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>1212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>1224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
Required output :
<query><matchAll>true</matchAll><row><columnId>11201</columnId><dataType>31</dataType><op>Like</op><val>North America - Houston</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Agreement Date Mismatch</val></row><row><columnId>11212</columnId><dataType>31</dataType><op>!=</op><val>Facility Type Mismatch</val></row><row><columnId>11224</columnId><dataType>31</dataType><op>Like</op><val>y</val></row></query>
View 7 Replies
View Related
Apr 16, 2013
I am learning oracle trigger, i have one query.
Can i increment the old column value in trigger.
eg: :new.cid := :old.cid+1;
is this is correct.
View 4 Replies
View Related
Apr 17, 2013
i am having a table with out pk along with data.
Now , i need to add one column to that table , and update this column with the sequence no
like 1,2,3...... upto the max no of records. but i have to do this with out using a sequence.
how can i do.
View 1 Replies
View Related
Sep 26, 2013
We've an accounts table that basically represents hotel chains & brands. an example shown below.
account_id chain_id brand_id service1
NULL NULL 111
NULL NULL 122
NULL NULL 11
Here I want to update the chain_id & brand_id which are currently NULL in order to make every row eligible for further processing.
There is another table(say chain_brand) which maintains the relationship between chain_id and brand_id. one chain_id can have multiple brand_ids eg.,
chain_id brand_id
101 2011 101
2012 102 2020
Now I need a script that could randomly pick values from chain_brand table and update the accounts table. condition is those values should be unique for an account_id eg.,
account_id chain_id brand_id service1
101 2011 111 101
2011 122 102 2020 11
so each account can be attached to only one chain_id and one brand_id.
View 2 Replies
View Related
Feb 16, 2011
The requirement I have is :
I have two tables eim_asset and eim_asset1.I want to update the table eim_asset1 using the following update SQL (Or Logic)
update eim_asset1
set emp_emp_login = (select login from s_user where row_id in
(select row_id from s_emp_per where row_id in
(select pr_emp_id from s_postn where row_id in
(select position_id from s_accnt_postn where ou_ext_id in
(select row_id from s_org_ext where row_id in
(select owner_accnt_id from s_asset where owner_accnt_id is not null)))))
It gives me the ORA error : ORA-01427:single-row subquery returns more than one row.know why I am getting it, because of the one-to-many relationship between owner accounts and their assets.
View 1 Replies
View Related
Jul 15, 2013
I have a multi record block based on a view. All records in the view are displayed in the block by use of Post-Query trigger when entering the form.
The block has 5 items as follows:
1) RECORD_STATUS = a non-base table column which is a checkbox.
2) ITEM_TYPE = a text-item which has an LOV attached.
3) ITEM_TEXT = a text-item which is free format text.
4) LAST_UPDATE_DATE a date column
5) STATUS = a text item either 'Open' or 'Closed'
The LOV is based on a table of Item Types with values say, 'Type1', upto 'Type9'.
I have a Wnen-New-Record-Instance trigger which 'Posts' changes to the database. This has been included as i want to limit the values of the ITEM_TYPE column to values which have not been previously used.
Consider this scenario...
The block has 3 records.
record 1 has 'Closed' status so no updates are allowed.
record 2 has 'Open' status so updating of Item_Text is allowed.
record 3 has 'Open' status so updating of Item_Text is allowed.
I check the RECORD_STATUS checkbox on record2.
(This sets the RECORD_STATUS checkbox to a checked value and changes the STATUS column to 'Closed' by When-Checkbox-Changed trigger.) At this point the record has not been saved so if you uncheck the checkbox , then the STATUS column will go back to 'Open'. However at this point i will leave it as Checked (Closed).
I then insert a new record, only values Item4 to Item 9 are correctly shown in the LOV. I select Item4.
I then go back to the previous record and uncheck the Checkbox to say that i wish to leave it 'Open' after all (in effect no changes have occurred), then the STATUS column correctly reverts back to 'Open' by my WCC trigger. If i then SAVE the changes, the new record has been inserted on the database correctly, however the LAST_UPDATED_DATE from the record which was checked and then unchecked has also been updated incorrectly even though no net changes have actually occurred.
(because i am using WNRI trigger to limit the List of Values on the LOV column, this has incorrectly set the previous records LAST_UPDATED_DATE column to be Sysdate.)
How can i stop this from happening?
View 1 Replies
View Related
Jun 26, 2013
I have two tables lets say TAB_A and TAB_B. I altered table B to include a new column from table A I wrote a merge statement as follows to merge the data
MERGE INTO TAB_AUSING TAB_BON (TAB_A.SECURITYPERSONKEY=TAB_B.SECURITYPERSONKEY)WHEN MATCHED THEN UPDATE SET TAB_A.PPYACCOUNT=TAB_B.PPYACCOUNT;
I know INSERT is for inserting new records UPDATE to my knowledge is to modify currently existing records (loosely) MERGE is one I rarely used, until this particular scenario. The code works perfectly fine, but I was wondering how could I write an update statement? Or in this scenario should I even be using an update statement?
View 4 Replies
View Related
Aug 27, 2013
I have more than 100 records in CSV format. I have to import these records to a particular table which already contains data. I have to do a multiple update at a time, based on the condition . ie., if field1 is '1' then update field2 as 'A0001' and if field1 is '5' then update field2 as 'A0007' . The values are not in an order. Is it possible.
View 1 Replies
View Related
Jun 14, 2012
I've a table with fields:
create table test
( f1 varchar2(10),
f2 varchar2(10),
f3 varchar3(10)
)
insert into test values ('d1','d2','d3');
insert into test values ('d10','d20','d30');
I want to update the fields of the table as per need i.e update only one field leaving all the data of the fields as it is. Suppose I want to update only f1 (from d1 to x1) field leaving f2, and f3 as it is. I've written stored procedure to update all the fields but do not know how to do it?
Quote:CREATE OR REPLACE PROCEDURE UPDATE_TEST
( U_F1 TEST.F1%TYPE,
U_F2 TEST.F2%TYPE,
[Code]....
View 7 Replies
View Related
Feb 28, 2013
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.
View 3 Replies
View Related
Oct 13, 2013
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.
View 5 Replies
View Related
Oct 20, 2012
I can't understand >>
1. Number 2 is less than number 100,
2. string '2' is greater than string '100'.
>> Numbers are ordered by numerical value; strings are ordered by alphabetical value. >>
View 1 Replies
View Related
Jun 28, 2010
If I have a "smart" key in a database, say one for which each byte of the key is meaningful, is it generally a good idea to string together a number of LIKEs with ORs? For example, if I want to select people where the last two bytes of that smart code are in a certain list and write:
where smart_key LIKE '%02'
or smart_key LIKE '%03'
or smart_key LIKE '%07'
or smart_key LIKE '%19'
or smart_key LIKE '%23'
or smart_key LIKE '%30'
or smart_key LIKE '%33'
or smart_key LIKE '%34'
or smart_key LIKE '%41'
etc.
Say I string a lot of those together, with 40 or 50 ORs. Right off the bat is it fair to say that's a bad query for Oracle, or do substrings of this sort generally perform well?
View 15 Replies
View Related
Apr 27, 2012
I found this query in my sub version repository and really wondering how this working
select to_char(to_date(1000000,'J') ,'JSP') string_value from dual;
View 1 Replies
View Related
Oct 21, 2012
I am using this Regexp to extract numbers from a string, and I doubt that there is a more efficient way to get this done:
SELECT regexp_replace (regexp_replace ( REGEXp_REPLACE (' !@#$%^&*()_+= '' + 00 SDFKA 324 000 8702 234 | " ' , '[[:punct:]]',''), '[[:space:]]',''), '[[:alpha:]]','') FROM dual
{code}
Is there a more efficient way to get this done ?
View 4 Replies
View Related
Jul 9, 2012
Im trying to generate this link on my result [URL].....
materialId and materialFolderId are the results of my query.. Im not sure how to combine my results and string on my query..
View 14 Replies
View Related
Feb 5, 2013
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
View 6 Replies
View Related
Jul 22, 2011
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.
View 7 Replies
View Related
