Home » SQL & PL/SQL » SQL & PL/SQL » repalce the first occurance of a character
repalce the first occurance of a character [message #312575] Wed, 09 April 2008 05:47 Go to next message
smithagirish
Messages: 10
Registered: March 2008
Location: kochi
Junior Member
Sir,

can anyone pls help me ....

i want to replace only the first occurance of a character in the given string

eg if the string is 'abcd,efg,hi,jk' then the output should be
'abcd efg,hi,jk'


thanks
Re: repalce the first occurance of a character [message #312579 is a reply to message #312575] Wed, 09 April 2008 05:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace( 'abcd,efg,hi,jk', '^([^,]*),', '\1 ') from dual;
REGEXP_REPLACE
--------------
abcd efg,hi,jk

1 row selected.

Regards
Michel
Re: repalce the first occurance of a character [message #312584 is a reply to message #312579] Wed, 09 April 2008 06:17 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
Michel , isn't that spoon feeding ? Cool
Re: repalce the first occurance of a character [message #312588 is a reply to message #312575] Wed, 09 April 2008 06:39 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
select regexp_replace( 'abcd,efg,hi,jk', '^([^,]*),', '\1 ') from dual;

This querry is not working on my PC.

can u tell me the reason behind this?

Thanks in Advance
Re: repalce the first occurance of a character [message #312589 is a reply to message #312588] Wed, 09 April 2008 06:41 Go to previous messageGo to next message
MarcS
Messages: 312
Registered: March 2007
Location: Antwerp
Senior Member
kecd_deepak wrote on Wed, 09 April 2008 13:39
select regexp_replace( 'abcd,efg,hi,jk', '^([^,]*),', '\1 ') from dual;

This querry is not working on my PC.

can u tell me the reason behind this?

Thanks in Advance


How do you know that it is not working? I'll make it a multiple choice question Smile

1 - You're not using a tool that understand SQL
2 - You don't get the expected result
3 - You get an Oracle Error

Re: repalce the first occurance of a character [message #312590 is a reply to message #312588] Wed, 09 April 2008 06:44 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
which oracle version you are using??

I think regexp is for 10g
Re: repalce the first occurance of a character [message #312591 is a reply to message #312575] Wed, 09 April 2008 06:44 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Hi,
I m sorry for partial information
i get following ORACLE error

select regexp_replace( 'abcd,efg,hi,jk', '^([^,]*),', '\1 ') from dual
*
ERROR at line 1:
ORA-00904: "REGEXP_REPLACE": invalid identifier
Re: repalce the first occurance of a character [message #312592 is a reply to message #312575] Wed, 09 April 2008 06:46 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
I am using ORACLE 9i
Re: repalce the first occurance of a character [message #312593 is a reply to message #312575] Wed, 09 April 2008 06:49 Go to previous messageGo to next message
kecd_deepak
Messages: 52
Registered: December 2007
Member
Thanks Guys,
These functions are for Oracle10g.
Re: repalce the first occurance of a character [message #312594 is a reply to message #312593] Wed, 09 April 2008 06:55 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Try Using Substr And Instr Functions

SELECT  
    SUBSTR('abcd,efg,hi,jk',1,INSTR('abcd,efg,hi,jk',',',1)-1) 
    ||SUBSTR('abcd,efg,hi,jk',INSTR('abcd,efg,hi,jk',',',1) 
    +1,length('abcd,efg,hi,jk'))  
FROM dual


Hope this helps
Re: repalce the first occurance of a character [message #312595 is a reply to message #312584] Wed, 09 April 2008 06:57 Go to previous message
Michel Cadot
Messages: 64119
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
MarcS wrote on Wed, 09 April 2008 13:17
Michel , isn't that spoon feeding ? Cool

It was a private joke for you. Cool

Regards
Michel

Previous Topic: Index creation!! issue in production (merged)
Next Topic: ora-00900
Goto Forum:
  


Current Time: Tue Dec 06 08:22:58 CST 2016

Total time taken to generate the page: 0.10006 seconds