Home » SQL & PL/SQL » SQL & PL/SQL » Query Help - String Manipulation (Oracle 10 G)
Query Help - String Manipulation [message #445636] Wed, 03 March 2010 01:27 Go to next message
sreekanth.nair
Messages: 5
Registered: October 2009
Junior Member
Hi All,
I have an issue in string manipulation. We are receiving strings like 'Finalized','Approved','Rejected' and I need to find the position of say "Approved" something like
instr('Finalized,Approved,Rejected', 'Approved'), since there are single quotes separator between each of the string I am unable to use instr. Can you please suggest a mechanism to sort out this issue. I tried using REPLACE but the issue is around single quotes & comma.
Thanks in advance.
regards,
Sreekanth
Re: Query Help - String Manipulation [message #445638 is a reply to message #445636] Wed, 03 March 2010 01:49 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
can you be bit more explicit?
select instr('Finalized'',''Approved'',''Rejected', 'Approved') from dual


' is the escape character

[Updated on: Wed, 03 March 2010 01:52]

Report message to a moderator

Re: Query Help - String Manipulation [message #445639 is a reply to message #445636] Wed, 03 March 2010 01:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63801
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It should be better you posted what you tried and the error you got.

To put a quote inside a string just double it (use 2 quotes).

Regards
Michel
Re: Query Help - String Manipulation [message #445640 is a reply to message #445638] Wed, 03 March 2010 01:59 Go to previous messageGo to next message
sreekanth.nair
Messages: 5
Registered: October 2009
Junior Member
Hi,
Thanks for your reply, but string is getting generated in Cognos and the query is being written in Cognos only, we can have string attached only at start & end. In cognos the query is somthing like instr(#status,'Approved') and the value inside status is 'Finalized','Approved','Rejected'. I am a db programmer & trying to help out our cognos team on this.
Regards,
Sreekanth
Re: Query Help - String Manipulation [message #445641 is a reply to message #445640] Wed, 03 March 2010 02:02 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
replace "'"(single quote)and ","(comma) with space(s)

And this is an oracle forum not cognos

[Updated on: Wed, 03 March 2010 02:03]

Report message to a moderator

Re: Query Help - String Manipulation [message #445655 is a reply to message #445641] Wed, 03 March 2010 03:34 Go to previous messageGo to next message
sreekanth.nair
Messages: 5
Registered: October 2009
Junior Member
Hi,
replace also won't work becasue of the string structure which we receive. Please treat this as oracle as I am concerned only on sql querry but end of it it is going to be used in cognos.
Regards,
Sreekanth
Re: Query Help - String Manipulation [message #445656 is a reply to message #445655] Wed, 03 March 2010 03:40 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
do a replace in the source(wherever the string is originating) if possible
Re: Query Help - String Manipulation [message #445658 is a reply to message #445656] Wed, 03 March 2010 03:45 Go to previous message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
SQL> declare 
  2  a varchar2(100) := 'ayush''anand';
  3  begin
  4  dbms_output.put_line(replace(a,'''','x'));
  5  end;
  6  /
ayushxanand

PL/SQL procedure successfully completed.

[Updated on: Wed, 03 March 2010 03:46]

Report message to a moderator

Previous Topic: Private Procedure
Next Topic: Analytic Function
Goto Forum:
  


Current Time: Sat Sep 24 18:04:13 CDT 2016

Total time taken to generate the page: 0.26554 seconds