Home » SQL & PL/SQL » SQL & PL/SQL » REPLACE string in where clause
REPLACE string in where clause [message #229985] Tue, 10 April 2007 08:28 Go to next message
dreamline
Messages: 8
Registered: July 2006
Location: Netherlands
Junior Member
Hi all,
I'm kinda new to the forum, but sofar I haven't been able to solve my problem. I was wondering if it was possible to use the replace string function inside the where clause? Sofar I haven't been able to successfully get it to work.

Example of what I wanna reach. I've got a database with several groups as varchar2 (examples: 01, 02, 04, 06, 10) And what I want to achieve is the following:
I want to use an sql prompt so you can give in several groups seperated by a space. Then in the sql command I want to use the replace function to get the apostrofes and comma's in the right place so I can use the different groups in a "in".

Here's an example:
accept grps prompt 'Groups (seperated by a space): '
select *
from <table>
where group in (chr(39)||REPLACE('&&grps',' ',chr(39)||','||chr(39))||chr(39) from dual)


I thought that's all I needed, however it still interprets a complete string and not the seperate groups. Is there a solution to my problem?

Any help is appreciated. Smile

Thanks.

[Updated on: Tue, 10 April 2007 08:30]

Report message to a moderator

Re: REPLACE string in where clause [message #230003 is a reply to message #229985] Tue, 10 April 2007 09:05 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
The following works:

SQL> accept grps prompt 'Groups (separated by a space): '
Groups (separated by a space): X Y Z
SQL> col in_list new_value in_list
SQL> 
SQL> 
SQL> select     chr(39)||REPLACE('&&grps',' ',chr(39)||','||chr(39))||chr(39) as in_list
  2  from dual
  3  /
old   1: select  chr(39)||REPLACE('&&grps',' ',chr(39)||','||chr(39))||chr(39) as in_list
new   1: select  chr(39)||REPLACE('X Y Z',' ',chr(39)||','||chr(39))||chr(39) as in_list

IN_LIST
-----------
'X','Y','Z'

SQL> 
SQL> select * from dual
  2  where dummy in (&in_list)
  3  /
old   2: where dummy in (&in_list)
new   2: where dummy in ('X','Y','Z')

D
-
X
Re: REPLACE string in where clause [message #230010 is a reply to message #229985] Tue, 10 April 2007 09:26 Go to previous messageGo to next message
dreamline
Messages: 8
Registered: July 2006
Location: Netherlands
Junior Member
Thanks Cthulhu,
I'll give that a try. I thought I already tried that and didn't work, but I might have jumped to that conclusion too fast. I'll give it a shot.

Once again thanks. Very Happy
Re: REPLACE string in where clause [message #230195 is a reply to message #230010] Wed, 11 April 2007 03:18 Go to previous messageGo to next message
dreamline
Messages: 8
Registered: July 2006
Location: Netherlands
Junior Member
Doesn't seem to work for me ... Sad Could it because of some settings that the above example isn't working? I hope so.

The second query doesn't get the selection from the dual table and stays empty.

Hope someone can help.. Smile

oud   1: select chr(39)||REPLACE('&&vgrp',' ',chr(39)||'\,'||chr(39))||chr(39) as test from dual
nieuw   1: select chr(39)||REPLACE('01 03 05',' ',chr(39)||','||chr(39))||chr(39) as test from dual
press return

Wo Apr 11                                                                                               
                                                                                                        

TEST
--------------
'01','03','05'

oud 198: and    adm.kode_groep in (&&test)
nieuw 198: and  adm.kode_groep in ()
and     adm.kode_groep in ()
                           *
FOUT in regel 198:
.ORA-00936: missing expression
Re: REPLACE string in where clause [message #230197 is a reply to message #229985] Wed, 11 April 2007 03:21 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Did you include the line:

col test new_value test
Re: REPLACE string in where clause [message #230199 is a reply to message #229985] Wed, 11 April 2007 03:22 Go to previous message
dreamline
Messages: 8
Registered: July 2006
Location: Netherlands
Junior Member
Oh, I overlooked that one.. I'll have a go at it right now.. Thanks.. Smile

Awesome.. It's working now.. Thanks a lot m8... Smile My problem is completely solved..

[Updated on: Wed, 11 April 2007 03:24]

Report message to a moderator

Previous Topic: Login.sql a very basic question
Next Topic: how to get rows of a table as columns
Goto Forum:
  


Current Time: Thu Dec 08 16:35:45 CST 2016

Total time taken to generate the page: 0.14961 seconds