PL/SQL :: Replace The Digits After A String CVV With X?

Jul 13, 2012

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;

View 11 Replies


ADVERTISEMENT

SQL & PL/SQL :: Way To Find Out Ending Digits In A String?

Dec 5, 2012

Is there a way to find out the ending digits in a string?

Examples of input and outputs:
'ABC123' -> 123
'ABC' -> NULL
'123ABC' -> NULL
'123ABC456' -> 456
'123ABC456QP98' -> 98

I have figured a way to do it by doing Reverse and re-reversing using: reverse(regexp_substr(reverse(p_string), '[ [:digit:]]*'))

But I'm sure there is a way to do it backwards without using reverse function, which I am not able to properly put in the syntax.

View 3 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

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

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 With The First Occurrence In The String

Nov 12, 2012

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

View 4 Replies View Related

Function To Replace All Occurrences Of A String?

Jan 9, 2007

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

View 4 Replies View Related

SQL & PL/SQL :: How To Manipulate String (Find And Replace)

Jun 29, 2012

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 Related

SQL & PL/SQL :: Extract Part Of String And Replace

Feb 13, 2012

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

View 3 Replies View Related

SQL & PL/SQL :: Search For A Pattern And Replace With A String?

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

PL/SQL :: Find And Replace Value In Delimited String

Sep 4, 2013

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?

View 10 Replies View Related

SQL & PL/SQL :: String Split / Replace Based On Character Length

Jun 19, 2013

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

View 7 Replies View Related

SQL & PL/SQL :: Show Last 4 Digits

Mar 4, 2010

I have a requirement where I have a value stored in varchar2(12), and no matter what the size of data, I should mask all the characters except the last 4

eg: - 23115645

after masking should be - xxxx5645,

create table test1(x varchar2(12))
insert into test1 values('34567745')
/
insert into test1 values('438765311')
/
insert into test1 values('1112345')
/
commit

[Code].....

y should store value of x in such a way that no matter what the size of x is (7 characters or 9), we should see only the last 4, but the remaining should be masked with xxxx

I tried the above update, but using substr, i am able to get this if x has 9 characters

not sure how to achieve this if x has 7, or 4 characters

View 22 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

How To Convert Hex To Decimal For Each 2 Digits

Dec 6, 2008

Actually I got a data in hex values and i want to convert it to datetime format. But before i convert the hex value into datetime format, i must convert the hex values into decimal val first for each 2 digits of hex values. This is an example of the input data:-

This is input data in hex value:-
STARTTIME : 080b1317021a

This is decimal value after convert from hex to decimal:-
STARTTIME : 081119230226

My problem is how could i convert hex to decimal in oracle? Below are my coding:-

case
when substr(LOAD_NGNSM.STARTTIME, 1, 2) not between '00' and '99'
or substr(LOAD_NGNSM.STARTTIME, 3, 2) not between '01' and '12'
or substr(LOAD_NGNSM.STARTTIME, 5, 2) not between '01' and '31'
or substr(LOAD_NGNSM.STARTTIME, 7, 2) not between '00' and '59'
or substr(LOAD_NGNSM.STARTTIME, 9, 2) not between '00' and '59'
[code]....

View 5 Replies View Related

Create Interactive Program To Add Numbers Between Two Given Digits Using SQL

Aug 11, 2008

How Do I Create An Interactive Program That Will Add Numbers Between Two Given Digits Using Sql. For Example:

Enter Start Num: 0
Enter End Num: 15
Then It Does 1+2+3+...........+14

View 2 Replies View Related

SQL & PL/SQL :: Update Table Removing First 2 Digits From Column?

Apr 10, 2013

I have a table like:

0035987850 P
0035987851 P
0035987852 P

I would like to update removing 00 in the first column. So after update to have:

35987850 P
35987851 P
35987852 P

View 5 Replies View Related

SQL & PL/SQL :: Selecting Numbers That Has Only 3 Digits After Decimal Point?

Mar 10, 2011

How to write SQL query for selecting all the values (numbers) in the table which has 3 digit after decimal point.

For example say A is the table it has following values

200.24
300.456
123.22
1234.344

From this I need only

300.456
1234.344

View 14 Replies View Related

Reports & Discoverer :: Digits Conversion - Report Developed In Builder

Oct 9, 2012

I am facing an issue here at my workplace, there is a report developed in report builder , the user wants to see the digits in arabic.

View 4 Replies View Related

Forms :: How To Force User To Enter Exactly 3 Digits Into Number Field

Mar 28, 2011

There is a text item on my form with NUMBER data type. I want to force the user to enter exactly 3 digits in to that text item. My current FORMAT MASK is set to 990. But this allows user to enter even a single digit where as my requirement is user should enter exactly 3 digits. Can we achieve this by changing the FORMAT MASK property?

View 6 Replies View Related

ORA-02085 - Database Link String Connects To String

Jun 19, 2012

I have this error (and solution):

ORA-02085: database link string connects to string

Cause: a database link connected to a database with a different name. The connection is rejected.

Action: create a database link with the same name as the database it connects to, or set global_names=false.
Where should I set global_names=false ?

View 7 Replies View Related

SQL & PL/SQL :: How To Find Whether Exact String Is Present / Not In Given String

Mar 14, 2013

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

View 3 Replies View Related

How To Use String Buffer Instead Of String Query

May 9, 2008

show an ex to use string buffer for select statemnt

View 1 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 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

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







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