Home » SQL & PL/SQL » SQL & PL/SQL » Can One Anyone help regarding this simple issue (Oracle 9i,Windows XP)
Can One Anyone help regarding this simple issue [message #379340] Tue, 06 January 2009 02:32 Go to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member


select replace(UPPER('MISC_DATA_STRAND,STRAND,ADDRESS'),'STRAND') k from dual;
 
K
--
MISC_DATA_,,ADDRESS

THE OUTPUT I WANT IS 

K
--
MISC_DATA_STRAND,,ADDRESS



HOW CAN I GET THIS OUTPUT IS THERE ANY OTHER FUNCTION TO DO THIS

Thanks in Advance
Re: Can One Anyone help regarding this simple issue [message #379346 is a reply to message #379340] Tue, 06 January 2009 02:46 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member

I hope your having Oracle 10g . use "Regexp_replace" instead.

Re: Can One Anyone help regarding this simple issue [message #379347 is a reply to message #379340] Tue, 06 January 2009 02:47 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
This might be one way:
SQL> SELECT REPLACE('MISC_DATA_STRAND,STRAND,ADDRESS', ',STRAND,', ',,') k
  2  FROM dual;

K
-------------------------
MISC_DATA_STRAND,,ADDRESS

SQL>
Re: Can One Anyone help regarding this simple issue [message #379353 is a reply to message #379340] Tue, 06 January 2009 03:09 Go to previous messageGo to next message
wmgonzalbo
Messages: 98
Registered: November 2008
Member
Let me try (hope im not hijacking OP's thread):

select replace ('MISC_DATA_STRAND,STRAND,ADDRESS',',STRAND',',') result from dual

RESULT
MISC_DATA_STRAND,,ADDRESS


Tried REGEXP_REPLACE, but im having probs..

select REGEXP_REPLACE ('MISC_DATA_STRAND,STRAND,ADDRESS',',[ [:alpha:] ],',null,1,1) result from dual

-- ',[url=/wiki/:alpha:]:alpha:[/url],' --> im trying to match for alphas enclosed with ',' (comma)

RESULT
MISC_DATA_STRAND,STRAND,ADDRESS


Edit: cant seem to post this code properly ',[ [ :alpha: ] ],''

Regards,
Wilbert

[Updated on: Tue, 06 January 2009 03:15]

Report message to a moderator

Re: Can One Anyone help regarding this simple issue [message #379358 is a reply to message #379353] Tue, 06 January 2009 03:22 Go to previous messageGo to next message
rakeshramm
Messages: 175
Registered: September 2006
Location: Oracle4u.com
Senior Member

BY USING THIS ALSO

select REGEXP_REPLACE ('MISC_DATA_STRAND,STRAND,ADDRESS','STRAND') result from dual

K
--
MISC_DATA_,,ADDRESS

THE OUTPUT I WANT IS 

K
--
MISC_DATA_STRAND,,ADDRESS




Experts Please Help
Re: Can One Anyone help regarding this simple issue [message #379368 is a reply to message #379358] Tue, 06 January 2009 04:05 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
SQL> select regexp_replace ('MISC_DATA_STRAND,STRAND,ADDRESS','STRAND',null,1,2)  from dual;

REGEXP_REPLACE('MISC_DATA
-------------------------
MISC_DATA_STRAND,,ADDRESS
Re: Can One Anyone help regarding this simple issue [message #379371 is a reply to message #379368] Tue, 06 January 2009 04:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select regexp_replace ('STRAND,MISC_DATA_STRAND,ADDRESS','STRAND',null,1,2)  from dual;
REGEXP_REPLACE('STRAND,MI
-------------------------
STRAND,MISC_DATA_,ADDRESS

1 row selected.

I don't think this is what OP will want.
I don't think the solution should be specific to this example.

Regards
Michel

[Updated on: Tue, 06 January 2009 06:12]

Report message to a moderator

Re: Can One Anyone help regarding this simple issue [message #379404 is a reply to message #379371] Tue, 06 January 2009 05:56 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
Of course ...

If u know the occurance of the pattern your searching ..i hav given solution specifically to the example he has given.It's not a generic one . Sad
Re: Can One Anyone help regarding this simple issue [message #379412 is a reply to message #379404] Tue, 06 January 2009 06:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe this one:
SQL> with data as
  2    ( select 'MISC_DATA_STRAND,STRAND,ADDRESS' val from dual
  3      union all 
  4      select 'STRAND,MISC_DATA_STRAND,ADDRESS' from dual
  5      union all 
  6      select 'MISC_DATA_STRAND,ADDRESS,STRAND' from dual
  7    )
  8  select val, regexp_replace(val,'(^|,)STRAND(,|$)','\1\2') val
  9  from data
 10  /
VAL                                      VAL
---------------------------------------- ----------------------------------------
MISC_DATA_STRAND,STRAND,ADDRESS          MISC_DATA_STRAND,,ADDRESS
STRAND,MISC_DATA_STRAND,ADDRESS          ,MISC_DATA_STRAND,ADDRESS
MISC_DATA_STRAND,ADDRESS,STRAND          MISC_DATA_STRAND,ADDRESS,

3 rows selected.

Regards
Michel
Re: Can One Anyone help regarding this simple issue [message #379414 is a reply to message #379412] Tue, 06 January 2009 06:41 Go to previous messageGo to next message
panyam
Messages: 146
Registered: May 2008
Senior Member
That's y Michel is great ........ Cool

[Updated on: Tue, 06 January 2009 06:42]

Report message to a moderator

Re: Can One Anyone help regarding this simple issue [message #379416 is a reply to message #379414] Tue, 06 January 2009 06:49 Go to previous message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Unfortunately, the original poster uses Oracle 9i so - he won't benefit much from regular expressions until he moves on to a more recent database version.
Previous Topic: job scheduling for tablespace quota
Next Topic: PL/SQL question
Goto Forum:
  


Current Time: Fri Dec 09 04:11:49 CST 2016

Total time taken to generate the page: 0.07799 seconds