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
ADVERTISEMENT
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
Jan 10, 2012
I want to convert a column value to a delimited string using a query.
Example
TableA
Col1 Col2 Col3
1 x200 MIS-X
2 x200 BTS-X
3 x200 TYR-X
4 x100 YRY-X
Select Col3 From TableA where Col2 = 'x200'
Expected Output:
'MIS-X','BTS-X','TYR-X'
View 4 Replies
View Related
Nov 12, 2010
I am expecting the input to my procedure will be in the following format
'AAA, aaa, Aa12|BBB, bbb, bb2B|dd3, DDDE,ddd67'
I need to convert it to nested table and when I query the nested table , the output should be
column_value
------------
AAA
aaa
Aa1
BBB
bbb
bb2B
dd3
DDDE
ddd67
View 9 Replies
View Related
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
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
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
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
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
View Related
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
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
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
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
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
Jun 17, 2010
I have a large table with a few billion rows. I need to find ITEM_NAME values in ITEM_MASTER table containing " " (spaces) and replace them with other values. This has to be done within the same script. The replacement values could be any characters or numbers, but not spaces.
The whole reason for this table is testing big databases, so it does not matter what it contains. The reason I need to replace them is that this table will be dumped to a space delimited flat file and spaces within values are not acceptable.
View 7 Replies
View Related
Aug 2, 2012
Below is a column 'ADDR' with the data (single column)
ADDR
--------
/shared/Folder_1 :^BIAdministrator:^BIAuthor:^BIConsumer:BISystemUser:OracleSystemUser:System:weblogic: :F
[Code]....
View 2 Replies
View Related
Jul 22, 2011
I am trying to update the greatest value in a column from a string of other column.
Ex: f the value is shown 10M+16M+25M-DG, then populate 25 only
so for that I had written query as follows:
update ANCHOR set IEL_STRAND_SIZE= greatest(
substr
(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','),
0,
length(REPLACE(REPLACE(REGEXP_REPLACE( F_TYP, '[A-Z]', '' ),'+',','),'-',','))-1))
The output is given as 10,16,25,but not as 25.so how could i write it?Do I need to implement procedure or arrays for it.
View 7 Replies
View Related
Jul 11, 2012
How can i identify all the occurences of raise_application_error(-20XXX, '<the message>'); within all database objects, and replace them with other text?
View 4 Replies
View Related
Apr 24, 2012
I am currently working on a Data Dictionary project where we need to run a few rules against the give data sources to see if they all comply together.
One of the rule is to check if the no. is negative or not. So for that what I tried to do was to check if first the field is number or not and then check on if it is negative or not.
This is the code I am currently trying on and is not looking good.
SELECT 1 FROM DUAL
WHERE decode(DECODE( TRANSLATE('-123.45','-0.123456789',''), NULL, 1,0), 1,substr('-123.45',1,1) ,' ' ) = '-'
View 13 Replies
View Related
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
May 16, 2008
How to find out lowercase char in a String in a SQL query
for example
"ORAclE" here c and l are lowercase so how can i find out this condition... is there any build in function in oracle?
View 13 Replies
View Related
Sep 8, 2011
How to find whether any character in a string is repeating or not
Eg: '123LH563' should return 'YES' , as 3 is repating there
'1234567' sould return 'NO' as there is no character which is repeating
View 8 Replies
View Related
Nov 7, 2011
I want to search for some specific string in packaged procedures source code, and want to list the name of those procedures along with package names.
View 17 Replies
View Related
Sep 19, 2013
I have a table like mentioned below
create table test1( test_no number, test_description varchar2(100));
insert into test1 values (1,'ABC£¥');
insert into test1 values (2,'BCD£¥');
Now I am selecting from the above table and the expected rest it should have shown is as shown below
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABC£¥
2 BCD£¥
But instead of showing the extended ascii characters, it is showing some different characters as shown below
select * from test1;
TEST1
-----------------------
TEST_NO TEST_DESCRIPTION
1 ABCLY
2 BCDLY
I have an requirement where i need to fetch the exact ascii characters for £ and ¥ instead of L and Y respectively.
View 1 Replies
View Related
Jun 1, 2011
Quote: I have a table(table name is names) with column as name(varchar) . I have the following data for name column.
Miss
Mississ
Mississipp
I would like to find a nearest match for Mississippi, that means sql should return row that contains Mississipp( Row #3)
If I try to find nearest match for Mississirr then sql should return row that has column value Mississ (Row#2)
Is this possible ? Here is the code for table creation and data.
create table names (name varchar2(20));
insert into names values('Miss');
insert into names values('Mississ');
insert into names values('Mississipp');
commit;
View 2 Replies
View Related
Sep 4, 2013
Insert into PROFILE
(INSTANCE, PROFILENAME, USER_DATA, UPDATE_DATE)
Values
(138, 'Test A', 'SRC!-1,ARCHIVE_OPT!-1,DATE_FIELD!155,DATE_RULE!1,DISTINCT!1', TO_DATE('01/20/2005 13:35:33', 'MM/DD/YYYY HH24:MI:SS'));
/
Insert into RULES
(ID, NAME)
Values
(155, 'DATE_TEST');
I want a code something of this sort:
select profilename from PROFILE where user_data like '%DATE_RULE!115%';
Output will be "Test A".Now, this is just a single value from RULES table used to find the data of PROFILE table.I will have to run the query on multiple values of RULES tables to find records containing a string format of sort "DATE_RULE!<rule_no>". How to search on WILD CARDs like these?
View 5 Replies
View Related
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
Jul 18, 2013
I need to update a column with tab delimited ie that column value should be tab delimited. Eg:- Url_name is the column and column values =[URL]. THe space between [URL] should be tab delimited. I wanted to use a update query to update this values with tab delimited.
View 2 Replies
View Related
Mar 17, 2013
A big table of size more than 4 GB from 10g DB needed to be extracted/exported into a text file,the column delimiter is "&|" and row delimiter is "$#".I cannot do it from TOAD as it is hanging while extraction of big table.
View 9 Replies
View Related
Mar 3, 2010
I have a requirement to get a delimited output file by executing a select query.
For e.g.
select id, name, age from customers;
i need the output as,
id,name,age
123,devi,23
34,abi,20
4900,infy,23
i tried select id||','||name||','||age from customers;
but am getting the following output....
id||','||name||','||age
123,devi,23
34,abi,20
4900,infy,23
But i want to remove those pipes in between the column name.
I tried colsep also... but there am getting the output as.,
id,name,age
123, devi, 23
34, abi, 20
4900, infy, 23
some unwanted spaces in between...but i want the output as this...
id,name,age
123,devi,23
34,abi,20
4900,infy,23
the query which am using is stored in a .sql file.
View 10 Replies
View Related
