SQL & PL/SQL :: Replace Y By N And N By Y
Sep 14, 2010I want to Update table value N by Y and Y by N.
ex.
col1
y
y
n
n
output:
col1
n
n
y
y
How can i do that ?
ADVERTISEMENT
SQL & PL/SQL :: REPLACE ON USER_MVIEWS / Number Cannot Be Replace
Jul 16, 2010I 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.
SQL & PL/SQL :: How To Replace Apostrophe
Jan 4, 2011I 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?
View 2 Replies View RelatedSQL & PL/SQL :: Replace String In 9i?
Jun 26, 2012I 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
SQL & PL/SQL :: Replace Comma With '�10'?
Jan 23, 2012TestCase
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
SQL & PL/SQL :: Replace Column Name
Sep 19, 2010For example i've a select query as below.
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)
PL/SQL :: How To Replace Execute Immediate In 11g
Jan 18, 2013creating 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?
View 3 Replies View RelatedPL/SQL :: Replace A String
Sep 26, 2013I 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 RelatedHow To Replace Leading Wildcards
Jan 16, 2011So 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.
SQL & PL/SQL :: Replace Repeating The Same Description With -DO-
Dec 19, 2012I am looking for oracle query to replace repeating description with -DO-. Sample Data is:
CREATE TABLE SCOTT.ITAX
(
VDATE DATE,
[Code].....
VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 CANOLA OIL 3 2500
10-DEC-12 CANOLA OIL 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 CANOLA SEES 3 5500
01-DEC-12 CANOLA SEES 3 5400
6 rows selected.
Required Output is:
VDATE DESCRIPTION ITAX AMOUNT
--------- -------------------------------------------------- ---------- ----------
14-NOV-12 CANOLA OIL 3 3500
25-NOV-12 -DO- 3 2500
10-DEC-12 -DO- 3 3300
01-NOV-12 CANOLA SEES 3 5600
10-NOV-12 -DO- 3 5500
01-DEC-12 -DO- 3 5400
SQL & PL/SQL :: INSTR With Replace Function
Aug 27, 2013I am trying this function
instr(','||replace(r_code,' ') ' ' || ', ' , ',' ||r_code || ' , ' )=0
i want to hardcode 'a1', 'a2' from r_code i dont want records from a1, a2
SQL & PL/SQL :: Replace Zero With Null Values
Jul 28, 2010I have a table abc with two column (marks,id) both can have any value.
Value of the id column is zero at several places.When I divide marks by id. I get divide by zero error.
how to replace zero with null.
SQL & PL/SQL :: Replace Command Is Not Working?
Jun 18, 2013database 10GR2, Character_set WE8ISO8859P1
The following command not working.
update consumerappliedform
set name =replace (name,'¿','‡');
What is the problem.
SQL & PL/SQL :: How To Replace Values Within Loop
Jun 26, 2012I 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
[code]....
SQL & PL/SQL :: Replace Characters In A String?
Apr 7, 2011replace 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.
SQL & PL/SQL :: How To Replace Table Name In Procedure
Apr 2, 2012I 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?
SQL & PL/SQL :: Update Using Replace Function?
May 5, 2011I have a table called email that contains a field called email
I have a few records in the table that contain example@hotmial.com and want to change the hotmial to hotmail
I also have some that are line example@hotmail.co and I want to change those to hotmail.com
so - I want to do a substring search and replace.
can the replace do this for me in one select statement - how do I structure the sql to do this?
SQL & PL/SQL :: Replace With The First Occurrence In The String
Nov 12, 2012I 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)")
PL/SQL :: Replace The Digits After A String CVV With X?
Jul 13, 2012I 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;
PL/SQL :: Create Or Replace View
Sep 11, 2012I 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.
View 3 Replies View RelatedSQL & PL/SQL :: Replace Unique Identifier?
May 27, 2012 How to replace uniqueidentifier in PL/SQL ,I have query like this,
@EmployeeID uniqueidentifier = NULL.
Function To Replace All Occurrences Of A String?
Jan 9, 2007Years 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);
SQL & PL/SQL :: Replace Multiple Value In Single Column?
Jun 7, 2011I am facing some problem, while fetching the result that I want to. I have a table with name "test", there are two columns:
"id" type int
"text_data" type varchar2(2000)
Sample Data:
ID TEXT_DATA
------- ------------
10 Hi Deepak, My designation id is dsha21. Thanks Rohit
Now I tried to replace the value for "Deepak","dsha21" and "Rohit" using nested replace function and I succeded but that was for static. Now while creating SQL procedure where I am going to make the values of "Deepak","dsha21" and "Rohit" some static variables. I want to pass the values to be replaced with static parameter.
If I give you simple example of my requirement that would be example of a sms send to all customers by a telephone company. Content is same only the Name of customer is replaced everytime.
Client Tools :: Replace & With Any Character
Dec 22, 2010I want replace '&' with any character.But when i use '&' in select statement.then it ask the value.
View 6 Replies View RelatedSQL & PL/SQL :: Replace New Line Character In Clob
Sep 2, 2010Replacing a new line character in clob column i Oracle 10g.I have tried replacing the new line using regexp_replace,replace and translate. I have also tried using lob_relace procedures.
View 2 Replies View RelatedSQL & PL/SQL :: How To Manipulate String (Find And Replace)
Jun 29, 2012I 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 RelatedSQL & PL/SQL :: Extract Part Of String And Replace
Feb 13, 2012I 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 RelatedSQL & PL/SQL :: Trigger Insert - Create Or Replace?
Jun 12, 2013I have make a new trigger.Create a trigger that inserting a new job_id MAX_SALARY assigned as the employee's salary more than 80 departmental charges
I have that code, is that correct?
CREATE OR REPLACE
TRIGGER TR27
AFTER INSERT ON JOBS FOR EACH ROW
BEGIN
(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARMENT_ID=80);
:NEW.MAX_SALARY := :OLD.MAX_SALARY;
END;
What I need to complete it?
SQL & PL/SQL :: Replace Not In / Not Exists With Outer Join
Aug 30, 2010I have a basic requirement to report rows from one table where the second table do not have the same record (basically same key).I understand that we could achieve this using not in or not exists clause. But I think using outer join probably be the simplest one. However, I could not achieve what I actually wanted using outer join
Test Case:
create table tab_1 (a number(1), b varchar2(20));
insert into tab_1 values (1,'one');
insert into tab_1 values (2,'two');
insert into tab_1 values (3,'three');
create table tab_2 (a number(1), b varchar2(20));
insert into tab_2 values (1,'one');
insert into tab_2 values (2,'two');
commit;
Now, I framed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+);
/
YA XA XB
---- ---- ----
1 1 one
2 2 two
3 three
In this case, as there is no corresponding record in second table, the value of YA is showing as null as shown above.Hence, I changed the query as
select y.a ya, x.a xa, x.b xb
from tab_1 x, tab_2 y
where x.a = y.a (+)
and y.a(+) is null;
/
YA XA XB
---- ---- ----
1 one
2 two
3 three
I dont understand why it is behaving in that way. I am rather expecting the output to come as:
YA XA XB
---- ---- ----
3 three
SQL & PL/SQL :: Replace Special Characters By SPACE?
Oct 18, 2010i have a database column holding first name and last name. I want to find out the special characters and convert it to empty space.
Eg : kishore's 001 ==> kishores 001
: jerryKumar* ==> jerryKumar
