Home » SQL & PL/SQL » SQL & PL/SQL » Find & Replace
Find & Replace [message #207649] Wed, 06 December 2006 07:25 Go to next message
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 #207650 is a reply to message #207649] Wed, 06 December 2006 07:29 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
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.
Re: Find & Replace [message #207654 is a reply to message #207650] Wed, 06 December 2006 07:54 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #207659 is a reply to message #207655] Wed, 06 December 2006 08:18 Go to previous messageGo to next message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
It's working fine but if i have more than 300 charcters, so if it's other function please suggest.
Re: Find & Replace [message #207660 is a reply to message #207659] Wed, 06 December 2006 08:23 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
300 characters ? What kinda characters are these ?

Have you considered the TRANSLATE function ?

Re: Find & Replace [message #207661 is a reply to message #207660] Wed, 06 December 2006 08:26 Go to previous messageGo to next message
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
Re: Find & Replace [message #207662 is a reply to message #207661] Wed, 06 December 2006 08:34 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
You should consider TRANSLATE then:

SQL> select translate('123#,;xyz','1#;','1') from dual;

TRANSLA
-------
123,xyz
Re: Find & Replace [message #207663 is a reply to message #207662] Wed, 06 December 2006 08:51 Go to previous message
kr_arvind
Messages: 85
Registered: April 2006
Location: Delhi
Member
Thanks translate function working fine
Previous Topic: Summing everything in the table on each row.
Next Topic: how to return all records i updated today?
Goto Forum:
  


Current Time: Sat Dec 10 01:15:59 CST 2016

Total time taken to generate the page: 0.21704 seconds