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.
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>
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).
I need to search a pattern backwards in a CLOB field.Function DBMS_LOB.instr does not work with '-1' offset (where to start the search) as instr does.
Parameters: instr(text_to_be_searched, pattern, offset, nth)
Example: I want to search 'Hello world' for the first instance of the letter 'o' starting from the end, backwards.As you can see, result for DBMS_LOB.instr is null when entered -1 for offset.
select
DBMS_LOB.instr('Hello world','o',-1,1) lob_i,
instr('Hello world','o',-1,1) std_i
from dual;
I want to replace numeric values of a specific format with 'X' , find the below example and note that the string in the example only for sample values and the strings may be different.
Eg.
Input String :
Ticket no 12343 , 1234567891234567 , origin-dxb , dest-lhr , 1234 5678 9012 2345 , address - rose wood
bldg 2444 , downtown ,london-33 .
Output string :
Ticket no 12343 , XXXXXXXXXXXXXXXX , origin-dxb , dest-lhr , XXXX XXXX XXXX XXXX , address - rose wood
bldg 2444 , downtown
I have a string. For example "I have too many files. There are 1000 files. I have to delete them." Sometimes the string can be "I have too many files. There are 115003 files. I have to delete them." Whatever the srting is, I need to change the string to "I have too many files. There are 10 files. I have to delete them." replace the "1000" or "115003" to "10". This portion of the string is always an integer. I use Oracle 11G2.
View 6 Replies View Relatedreplace the first 5 commas with the character '|' in the below string:
'Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver'
I tried:
SELECT regexp_replace('Red, White, Blue, Purple, Pink, Green, Yellow, Gold and many others, like Black and Silver',
',','|',1,5) from dual
but it only replaces the 5th comma.
I am using regexp_replace function to replace the string between the double quotes with the first occurance but i am able to replace first occurance but I am not getting remaining string.
For example:
select REGEXP_replace('Parent.addChildByName("DS-Id of OAL(BROBA)")||parent.add("DS-Id of OAL(BROBA)")','["]:print:+:punct::print:*["]','XXXX') from DUAL
O/P: Parent.addChildByName(XXXX)
Expected O/P : Parent.addChildByName(XXXX)||parent.add("DS-Id of OAL(BROBA)")
I am using the oracle 10g, I am trying to write the a sql query by which in a below mention patterns I can replace the digits after a string 'CVV' with X. The no of X will be equal to the no of digits after CVV
1. BTA CVV 8810
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVV498
3. C***CVV VI 569***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV 063/dffddf:dfdfdfd
5. (T) CVV 4671
6. MS-ACEEML/CVV
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES
AFTER a change above data should look like as mentioned below
1. BTA CVV XXXX
2. VISA PARTICULAR CCVIXXXXXXXXXXXX5474/1012 CVVXXX
3. C***CVV VI XXX***
4. dskdfjdkgjdfk: FP CCVI,XXXXXXXXXXXX0031/0711/CVV XXX/dffddf:dfdfdfd
5. (T) CVV XXX
6. MS-ACEEML/CVV -- no change for this
7. O/AXXXXXXXXXXXX1007EXP1210/CVVXXXXXXXXXXX4664/MEETING CARD FOR AMEX PARTNER OFFICES --- -- no change for this
Initially I tried to find the position of CVV in any string and then from that position take the digit and replace them with X. I tried the below code which is not complete
select REGEXP_INSTR('BTA CVV XXXX','CVV',1,1) from dual;
Assuming I have the following table with its data:
Quote:
| code | description |
| A | $abc + xyz = $cba - 2 |
| B | ($12345 + $12345)/3 |
| C | $poke + $abc = $abc + 456 - 789 |
How can I do a selective search on "description" column for any word that starts with the "$" sign? In the end, this is the resultset that I am expecting:
Quote:
| $ | count of $ |
| $abc | 3 |
| $cba | 1 |
| $12345 | 2 |
| $poke | 1 |
I have table having a single column a, which contain the values "HP Laptop", can search it in either way , i mean to say either user input the HP Laptop or Laptop HP? is it possible via SQL query ?
View 11 Replies View RelatedYears ago, someone created a database in Oracle that was carried over and now sits in Oracle 10g. I am developing an application that queries this database and returns the result as XML.
Many (thousands) of rows have an item description that contains an ampersand. I want each of these pieces of data to have it written as & amp; (had to add a space so it would show up here, but you know what I mean) instead of &, but I don't feel like doing thousands of UPDATEs to change this.
Does Oracle have any global find/replace functions that I can call? I'd rather do one update statement that replaces all occurances of & with & amp; but I can't seem to find a function that will do this.
I'm thinking something that would work like:
UPDATE table SET column1=REPLACE(column1,oldstr,newstr);
I am required to manipulate a string in this way.For example ABCDEF)* to $ABCDEF). So I should find * and delete it and instead put $ in the beginning of string.
View 9 Replies View RelatedI have written one program that inserts one field in table item_master based on existing field,for eg, its like old field is 'HEB240x240x10x17x13000mm, S 275 JR' - and i want to replace the 5 digits before mm i.e 13000 needs to be replaced by 6000 or 4000 based on generated values and the new item will be like 'HEB240x240x10x17x6000mm, S 275 JR' or 'HEB240x240x10x17x4000mm, S 275 JR'.
View 3 Replies View RelatedI have a requirement, where i need to find and replace values in delimited string. For example, the string is
"GL~1001~157747~FEB-13~ CREDIT~ A~N~ USD~ NULL~".
The 4th column gives month and year. I need to replace it with previous month name. For example:
"GL~1001~ 157747~ JAN-13~ CREDIT~ A~N~USD~NULL~".
I need to do same for last 12 months. I thought of first devide the values and store it in variable and then after replacing it with required value, join it back. I just wanted to know if there is any better way to do it?
I have one table for eg. TB_Fruits.
In that i have one column FruitsName(Varchar)
In that column i am storing string in comma separated values.
Select FruitsName from tb_fruits;
Result: orange,banana,apple
Now the issue is suppose if i try to insert any of these fruits name again then it should not allow me to insert.
Suppose now if i try to insert ('grapes,banana')
or
('apple,grapes')
the orange,banana,apple can be in any position.
How to check if any of these names already exist or not in the column fruitsname?
I cannot use like or INstr function here. because the position is not fixed not even string.
My Scenario is'....456re0,50kg400,500rfabs43qre30,25kg150,354rf658....'
there is possible,using regexp_substr or other way to get the values, 0,50 and 400,500 and 30,25 and 150,354? I'm using [^re]+[$kg] and the string comes, but only the first occurence..
I want to select the view names from text column in dba_views.
View 7 Replies View RelatedTrying to get a number out of an error backtrack
06512: at "SCHEMA.PROCEDURE", line 4
I only need to take the 4 out of this message. Of course the number might be anything from 1 to 10000.
The database languages might differ so I can't do this using
v_line_no:=SUBSTR(v_line_no,INSTR(v_line_no,'line')+5)
As it would not find any 'line' inside the string.
How can I search the tree with a given string?I don't find any function like "search(...)".I want to find the tree nodes which include the given string.
View 5 Replies View Relatednotes column having 2000 characters max, i want my string output based on 35 characters, ya i need to replace tag after 30 characters in the string.. I need out put as "hi hello how are you doing out there, similar i need to calculate the sting length and have to split it 35+35+35..
This i tried
select substr(note,1,(instr(note, ' ',35)))||'
'||substr(note,instr(note, ' ',35),(instr(note, ' ',35)))notes from test
In Application Builder, is there a way to searching the application and replacing text within it?
I have an application item named "FORMAT_TYPE1". I would like to perform a find and replace action (just like a word processor) where any part of the application that has name or text "FORMAT_TYPE1" in whole or in part is replaced by the text "FORMAT_CATEGORY1". For example:
FORMAT_TYPE1 becomes FORMAT_CATEGORY1
&FORMAT_TYPE1. becomes &FORMAT_CATEGORY1.
The find/search would do it in any attribute, source code in the application. There is only a Search Application feature, which is useful in identifying places, but lacks any replace function. Is there something else that will do this, besides exporting, editing the source code in a text editor, and then importing the application again?
I am trying to search a word which starts with 'FRA' in any columns and any tables.
I am unable to find what is generating a join datasets in the webservice from teh database as it is not apparent within the 100 tables.
I ahve looked into
Re: How to search in all rows and all columns?
Re: SQL Search Query?
but none of these queries is working out for me as I am a user with no tables on its own but rather a user quering other tables.I think its a tweak on which dat a dictionary I can view
select distinct substr (table_name, 1, 14) "Table",
substr (t.column_value.getstringval (), 1, 50) "Column/Value"
from all_cons_columns,
table
(xmlsequence
(dbms_xmlgen.getxmltype ('select ' || column_name
|| ' from ' || table_name
|| ' where upper('
|| column_name
|| ') like upper(''%' || 'fra'
|| '%'')'
).extract ('ROWSET/ROW/*')
)
) t
order by "Table";
running teh above query got me thsi error:
ORA-19202: Error occurred in XML processing
ORA-00942: table or view does not exist
ORA-06512: at "SYS.DBMS_XMLGEN", line 288
ORA-06512: at line 1
19202. 00000 - "Error occurred in XML processing%s"
*Cause: An error occurred when processing the XML function
*Action: Check the given error message and fix the appropriate problem
I have a requirement in which i have list pdf files(from thousands of pdf files) having particular string from a command prompt.I have tried find command but that has some limitation. its working upto 3 digit string only.how can i acheive this.remember i have to do this through command prompt only.
View 1 Replies View RelatedI 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.
I have 70 materialized views where I need to replace the FROM SCHEMA1 TO FROM SCHEMA2...To quickly to do the fix for all the 70 views..
SELECT REPLACE (QUERY, 'schema1', 'schema2' )FROM USER_MVIEWS ;
But It throws me an error that Number cannot be replace.
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-+/*().]*).*
Is there a method or a tool out there that can do a search through an Oracle Schema to find objects ( tables, fields, stored procedures, etc) containing a specified character pattern ? For example : I would like to return all of the tables that contain fields containing the character string "ABC"
View 1 Replies View RelatedI 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.
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.
