Home » SQL & PL/SQL » SQL & PL/SQL » Find & Replace
Find & Replace [message #207649] |
Wed, 06 December 2006 07:25  |
kr_arvind
Messages: 85 Registered: April 2006 Location: Delhi
|
Member |
|
|
Hi,
Please help me how can find more than one string from replace function or any other fuction.
Replace('123,#;xyz','#','')
But it's possible from below type
replace('123,#;xyz','#',';','')
Arvind
|
|
|
|
Re: Find & Replace [message #207654 is a reply to message #207650] |
Wed, 06 December 2006 07:54   |
kr_arvind
Messages: 85 Registered: April 2006 Location: Delhi
|
Member |
|
|
ebrian wrote on Wed, 06 December 2006 18:59 | What version of Oracle are you using? If on 9i, you'd probably have to do nested REPLACE functions, with 10g, regex could potentially help you.
|
i have using 9i
|
|
|
Re: Find & Replace [message #207655 is a reply to message #207654] |
Wed, 06 December 2006 07:58   |
 |
ebrian
Messages: 2794 Registered: April 2006
|
Senior Member |
|
|
Actually, in your example above, if you are looking to replace both the '#' and ';' characters and they will always appear next to one another, you could simply use:
replace('123,#;xyz','#;')
However, if these two characters may appear in random order, then you could use the nested REPLACE as:
SQL> select replace(replace('123#,;xyz','#'),';') from dual;
REPLACE
-------
123,xyz
Notice I put the ',' between the # and ;
[Updated on: Wed, 06 December 2006 08:12] Report message to a moderator
|
|
|
|
|
Re: Find & Replace [message #207661 is a reply to message #207660] |
Wed, 06 December 2006 08:26   |
kr_arvind
Messages: 85 Registered: April 2006 Location: Delhi
|
Member |
|
|
All are special charchters.
For Ex-
SELECT
B.customer_id,
trim(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
replace(
REPLACE(UPPER(b.ccfname||b.cclname||b.ccstreet||b.ccaddr1||b.ccaddr2||b.ccaddr3||b.cccity),' ',''),
'/',''),
'-',''),
'!',''),
'@',''),
'#',''),
'$',''),
'%',''),
'^',''),
'&',''),
'*',''),
',',''),
';',''),
'(',''),
')',''),
'=',''),
'+','')
) address
FROM
MIS_MAS_PRE_CCONTACT_ALL b
|
|
|
|
|
Goto Forum:
Current Time: Sat Feb 08 16:02:21 CST 2025
|