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.
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.
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
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;
Years 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.
I 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'.
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.
I 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?
notes 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
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
I have a table MOM i.e. Minutes of meeting where important points in a meeting are captured. Now these points may include words like, "don't" or "can't" which will be recorded first in a text file and later copied to the table MOM. Rest of the details are unimportant. All I want to know is how do I enter these words without getting the ORA-01756 error? Do I need to always correct them before entering or is there perhaps another way?
DROP TABLE test ; CREATE TABLE test (id NUMBER, cnt_str VARCHAR2(200)); INSERT INTO test (id, cnt_str) VALUES (1,'AKRN000002,1451-1473,00000A,74,AKRN000002,1475-14791000000A,8010AKRN000002,1481-1492,00000A,9310AKRN000002,1494-1500')
[code]...
The requirement is in each of the string where there is comma after the number and the number is prefixed by "-" character, the comma after the number should be replaced by '10'.
For example in the second record where ID = 2, CNT_STR is '00000B,1-251000000D,26-32,ADTW000301,2858-2875'. In this string -32, should become -3210 and resulting string should be '00000B,1-251000000D,26-3210ADTW000301,2858-2875'
Expected Result.
ID CNT_STR -------- ------------------------------------- 1 AKRN000002,1451-14731000000A,7410AKRN000002,1475-14791000000A,8010AKRN000002,1481-14921000000A,9310AKRN000002,1494-1500 2 00000B,1-251000000D,26-3210ADTW000301,2858-2875 3 AKRN000001,1126-12001000000B,501-525
1.select store_name, store_id from stores 2.select store_name, (select store_id from inventory where store_id=<somevalue>) from stores 3. select store_name, store_id from stores where store_id in(select store_id from stores1)
based on above examples i need to replace all the columns(oly the column names not the column names in the subquery) in a sql query .
is there is any oracle function to achieve this?
and the result shud be as follows if i replace with STORE_DET.
1.select STORE_DET from stores 2.select STORE_DET from stores 3. select STORE_DET from stores where store_id in(select store_id from stores1)
creating package and i need not to use execute immediate. There are some dynamic build plsql's and sql's stored in global lists. Any other possibility to run them without execute immediate?
So many of the queries in our database query by an account but an account is allowed to vary by prefix and so queries are written similar to account like '%suffix'
Our DBA has rejected the use of context indexes and friends to deal with this.
as an aside and I'm probably going to regret going into this much detail but our model is
A->>B->>C
typically the queries want to fetch C's for a customer defined by A.account
A, B, C are all partitioned with partition key created_date
however, only C is typically queried with created_date as a qualifier. A and B are related by joins from A->>B and B->>C
when queries are written to use '%' leading wildcards often it results in a full table scan across multiple partitions. on any given day all we care about are the most recent C's for the customer where customer is defined by A.account.
I recently had an idea whereby fields such as A.Account could also be kept(denormalized) in the C table
the idea is if the queries were written to use C.account like '%bla' instead of A.account like '%bla' then because C is always qualified with the partition key ie. C.created_date that would at worst result in a full partition scan only. this is considerably less expensive than doing a full table scan of A. The IO cost of doing that is huge.
populating the C.account is a simple before insert or update trigger. when you insert a C you have a foreign key to a B and B has a foreign key to A. A and B are always created before any C is seen for any given C. When a C is seen A and B already exist in the database.
I have one emp table, in which i have sal, ename,empno etc columns. Now i am trying to replace each digit of salary with '*'. like if salary is 10000(it has five digit) then it will be replace by *****, but unable to do so. I don't how to achieve this, can we use translate or replace function in this.
Well i have achieved this by using case statement but where hardcoding is done...see the case below:-
select substr(ename,1,8),case when length(sal)=2 then '**' when length(sal)=3 then '***' when length(sal) = 4 then'****' when length(sal)=5 then
I need to create a procedure which inserts values from a temporary table into the main table the columns names in temporary table are same for all temporary tables only the table name will be replaced the skeleton procedure code will look like this.
create or replace procedure load_data as
select c_tables is select table_name from user_tables where table_name like 'TEST%'; V_tbl varchar2(30); BEGIN OPEN C_TABLES; LOOP FETCH C_TABLES INTO V_TBL;
[code].....
The logic here is to insert values from v_tbl variable table into sales_target table.
when I compile it doesn't like using V_TBL variable in the procedure?
I am about to pass 1Z0-051 and have been cramming. Ran across this scenario: Create or replace view. Correct answer: allows insert from view in multitable insert statement. Can I actually insert data from a view? My understanding is no data actually exists in a view. This has created a cavernous feeling of inadequacy.
