PL/SQL :: Replace All Characters Of Variable With Number?

Jun 3, 2013

i am working on the oracle 10g.currently i want to build one procedure,where i get a variable which contain character as well as number.my aim in the procedure is to replace all the character like ( a,b..z) in this variable with the number field.

example

suppose i have one variable say

var := 'MALT011000012345MTLCAST001SMT84'
now i want to replace each character say
A with value 23
B with value 56
L with value 99
9 i will get these value form another table).

i will have handle thousands of variable and they can start with any character and they contain character anywhere.

View 4 Replies


ADVERTISEMENT

SQL & PL/SQL :: Replace Characters In A String?

Apr 7, 2011

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

View 3 Replies View Related

SQL & PL/SQL :: Replace Special Characters By SPACE?

Oct 18, 2010

i 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

View 2 Replies View Related

SQL & PL/SQL :: Replace Non Alpha Numeric Characters

Jul 1, 2004

I have a function that will replace the contents of the input parameter and replace any non-numeric characters.� I just want to know if there is a more efficient way to code this (oracle 8i or higher).

function strip_non_numeric(p_string in varchar2) return varchar2 is
Result varchar2(100) := '' ;
x_length number;
begin
SELECT LENGTH(p_string) INTO X_LENGTH FROM DUAL;
FOR i in 1..X_LENGTH LOOP

[Code]....

View 6 Replies View Related

SQL & PL/SQL :: REPLACE ON USER_MVIEWS / Number Cannot Be Replace

Jul 16, 2010

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.

View 14 Replies View Related

Forms :: Variable Can't Hold 4000 Characters

Dec 22, 2011

I have error message when running duplicate : FRM-21011: PL/SQL unhandled exception ORA-06502..I'm trying to hold 4000 characters in a variable like what do below:

if s_str is NULL then
s_str := eachcol.column_name||'{{'||
name_in(name_in('system.cursor_block')||'.'||eachcol.column_name)||'{{';
else
s_str := s_str||eachcol.column_name||'{{'||
name_in(name_in('system.cursor_block')||'.'||eachcol.column_name)||'{{';
end if;

It's simple variable to hold value but still can't get by large string though.

View 1 Replies View Related

Forms :: Oracle Global (variable Name) Can't Hold 4000 Characters

Dec 13, 2011

In current form, i use *pll file to pass Global.<variable name> to the form *.fmb

The problem is that if i copy a string of 4000 characters (which i need to) to Global.<variable name>, it will automatically cut a whole chunk to shorter string (less than 1000).

Is there a better way to that Global.<variable name> can hold 4000 characters?

View 1 Replies View Related

SQL & PL/SQL :: Replace Double Space With Single Space And Also Remove Junk Characters

Jul 1, 2013

I want to replace double space with single space and also remove junk characters from the data. How can I do that?

CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"

View 8 Replies View Related

PL/SQL :: How To Convert Number (15 Characters) To Date

Jun 23, 2013

One table ACCOUNT_T.CREATED_T is number (38). It is mapped to /ACCOUNT's PIN_FLD_CREATED_T, which is timestamp. ACCOUNT_T table has one record, CREATED_T = 1362063600. This field is displayed at the GUI (Customer Center) as Feb 28, 2013 So my question, how to convert the number value (1362063600) to a date value (Feb 28, 2013)?

View 4 Replies View Related

View With Variable Number Of Columns

Oct 21, 2011

I've created a stored procedure which creates itself a view (a MV to be honest); the instructions to create this m.view are dinamically built insinde my procedure, so each time i run it, based on the different input parameters, i've got a different result (my output m.view can have three colums the first time, or ten the next time) how can I read my output view to put the data into file? I've tried with "select * bulk collect into my_array from my_ output_ view"...after declaring "my_array" as a varying array with the max number of colums I could ever have...but nothing: if the array dimension doesn't match the number of columns that i've on my view, i.e. i receive "ora-00947 not enough values" error.

Is there a method to dimension dinamucally the array to store my data? Or should I change the code to fetch some other way the data i need to put to a file?

View 2 Replies View Related

SQL & PL/SQL :: Checking Variable Number Of Columns?

Apr 12, 2010

My goal is this:I have a table which is being updated/changed by lay people with certain types of values. Constraints are given to them, but they need not conform to them as they update the table in excel.Now, I want to validate this table every-time before I use it. i.e. implement a script which can be run to verify is all the values are in the right format for further usage.

I have a variable number of columns (i.e. users can add further columns as their requirements change).From columns 3 to 'n' (depending on table given) the values should be 'Yes' or 'No'. How do I check this for a variable number of columns in PL/SQL?

View 11 Replies View Related

Forms :: ORA-01036 - Illegal Variable Name / Number

May 30, 2012

I have developed a form which is running fine most of the time,

but some time it gives error

unable to insert record and if we see detail it shows

ORA-01036: illegal variable name/number

error.jpg is attached. if there is some problem, it should not pass any entry and if there is no problem then why it gives error of illegal variable?

View 3 Replies View Related

How To Store Total No Of Updated Rows (number) In A Variable

Jun 21, 2011

how to store total no of updated rows (number) in a variable after executing an updation query using script

View 2 Replies View Related

SQL & PL/SQL :: Procedure To Compute Ratios Across Variable Number Of Months

Nov 19, 2011

the problem below:

I have a table AlertData below:

DeptIDMONTHCount
192010-041392
192010-051134
192010-061094
192010-071333
292010-042217

[Code]...

Within each DeptID group I need to calculate absolute change of 'Count' column between previous and current months and compare change value with threshold.

If ratio >= threshold N number of times I need to make a note of that event. Threshold = 0.1 N = 2 - alert needs to exceed threshold two consequtive times

Here is data processing algorithm:

1. Calculate change between month 2010-04 and 2010-05: abs((1134/1392 - 1))= 0.18;
2. check change value against threshold: 0.18 > 0.1
3. Threshold was exceeded, set alert_fired_cnt counter to = 1
4. Once alert fired it creates a baseline for comparison - I need to use Count from month 2010-04: We're now in month 2010-06: abs(1094 / 1392 - 1)=0.21
5. check change value against threshold: 0.21 > 0.1
6. Threshold was exceeded, increment alert_fired_cnt counter by 1 = 2
7. At this point alert exceede threshold two times, I need to set a alert_triggered flag = 1 and reset alert_fired_cnt = 0 for further calculations
8. We're in montn 2010-07: abs(1333/1294-1)=0.03
8. check change value against threshold: 0.03 < 0.1
9. Since threshold was not exceeded, keep alert_fired_cnt counter to = 0

Above algorithm needs to be run for all DeptID groups.

I load above data into an associative array and loop through elements. I am having trouble keeping computations within each DeptID group.

View 18 Replies View Related

SQL & PL/SQL :: Add Variable Number Of New Columns To Existing Table Temp

Feb 24, 2010

I want to add a variable number of new columns to an existing table temp (with column provided).

Example:

NewColumnNo = 4
-> the columns shall be named rate_1, rate_2, rate_3 and rate_4
-> the values shall be = Column / NewColumnNo

The result shall be like this:

create table temp_res (prodid integer, rate_1 number, rate_2 number, rate_3 number, rate_4 number);
insert into temp_res values(1, 0.25, 0.5, 0.75, 1);
insert into temp_res values(2, 0.25, 0.5, 0.75, 1);
insert into temp_res values(3, 0.25, 0.5, 0.75, 1);
insert into temp_res values(4, 0.25, 0.5, 0.75, 1);
insert into temp_res values(5, 0.25, 0.5, 0.75, 1);
insert into temp_res values(6, 0.25, 0.5, 0.75, 1);
insert into temp_res values(7, 0.25, 0.5, 0.75, 1);
insert into temp_res values(8, 0.25, 0.5, 0.75, 1);
insert into temp_res values(9, 0.25, 0.5, 0.75, 1);

View 8 Replies View Related

SQL & PL/SQL :: Removing Special Characters And Get Desired Characters From Column Values

Jul 23, 2013

create table test
(
name varchar2(50),
descd varchar2(50)
)
insert into test values ('kethlin','da,dad!tyerx');
insert into test values ('tauwatson','#$dfegr');
insert into test values ('jennybrown','fsa!!trtw$ fda');
insert into test values ('tauwatson','#$dfegr ,try');

how do I get the first three characters and last three characters from name field and remove all the junk characters from descd field?

so my o/p be like;

Quote:('ketlin','dadadtyerx')
('tauson','dfegr')
('jenown','fsatrtw fda')
('tauson','dfegr try')

View 6 Replies View Related

SQL & PL/SQL :: Replace Y By N And N By Y

Sep 14, 2010

I 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 ?

View 10 Replies View Related

SQL & PL/SQL :: How To Replace Apostrophe

Jan 4, 2011

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?

View 2 Replies View Related

SQL & PL/SQL :: Replace String In 9i?

Jun 26, 2012

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

View 6 Replies View Related

SQL & PL/SQL :: Replace Comma With '�10'?

Jan 23, 2012

TestCase

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

View 4 Replies View Related

SQL & PL/SQL :: Replace Column Name

Sep 19, 2010

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

View 3 Replies View Related

PL/SQL :: How To Replace Execute Immediate In 11g

Jan 18, 2013

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?

View 3 Replies View Related

PL/SQL :: Replace A String

Sep 26, 2013

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 Related

How To Replace Leading Wildcards

Jan 16, 2011

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.

View 3 Replies View Related

SQL & PL/SQL :: Replace Repeating The Same Description With -DO-

Dec 19, 2012

I 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

View 2 Replies View Related

SQL & PL/SQL :: INSTR With Replace Function

Aug 27, 2013

I 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

View 1 Replies View Related

SQL & PL/SQL :: Replace Zero With Null Values

Jul 28, 2010

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

View 22 Replies View Related

SQL & PL/SQL :: Replace Command Is Not Working?

Jun 18, 2013

database 10GR2, Character_set WE8ISO8859P1

The following command not working.

update consumerappliedform
set name =replace (name,'¿','‡');

What is the problem.

View 3 Replies View Related

SQL & PL/SQL :: How To Replace Values Within Loop

Jun 26, 2012

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

[code]....

View 27 Replies View Related

SQL & PL/SQL :: How To Replace Table Name In Procedure

Apr 2, 2012

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?

View 5 Replies View Related







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