OraFAQ Forum: SQL & PL/SQL » repalce the first occurance of a character

Home » SQL & PL/SQL » SQL & PL/SQL » repalce the first occurance of a character

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

repalce the first occurance of a character [message #312575]

Wed, 09 April 2008 05:47

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312579 is a reply to message #312575]

Wed, 09 April 2008 05:58

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312584 is a reply to message #312579]

Wed, 09 April 2008 06:17

MarcS
Messages: 312
Registered: March 2007
Location: Antwerp

Senior Member

Michel , isn't that spoon feeding ? Cool

Report message to a moderator

Re: repalce the first occurance of a character [message #312588 is a reply to message #312575]

Wed, 09 April 2008 06:39

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312589 is a reply to message #312588]

Wed, 09 April 2008 06:41

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312590 is a reply to message #312588]

Wed, 09 April 2008 06:44

rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA

Senior Member

which oracle version you are using??

I think regexp is for 10g

Report message to a moderator

Re: repalce the first occurance of a character [message #312591 is a reply to message #312575]

Wed, 09 April 2008 06:44

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312592 is a reply to message #312575]

Wed, 09 April 2008 06:46

kecd_deepak
Messages: 52
Registered: December 2007

Member

I am using ORACLE 9i

Report message to a moderator

Re: repalce the first occurance of a character [message #312593 is a reply to message #312575]

Wed, 09 April 2008 06:49

kecd_deepak
Messages: 52
Registered: December 2007

Member

Thanks Guys,
These functions are for Oracle10g.

Report message to a moderator

Re: repalce the first occurance of a character [message #312594 is a reply to message #312593]

Wed, 09 April 2008 06:55

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

Report message to a moderator

Re: repalce the first occurance of a character [message #312595 is a reply to message #312584]

Wed, 09 April 2008 06:57

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Dec 07 05:19:56 CST 2024