Home » SQL & PL/SQL » Client Tools » & sign in where clause
& sign in where clause [message #263004] Tue, 28 August 2007 14:11 Go to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
I have a query where I have an & sign in one of the fields as follows:-

 
SELECT *
WHERE short_nm = 'S&P400'
FROM options 




On running this query I get

Enter value for p400:

what should I replace 'S&P400' with?
Re: & sign in where clause [message #263010 is a reply to message #263004] Tue, 28 August 2007 14:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Many solutions.
Here's one: "set define off"

Regards
Michel
Re: & sign in where clause [message #263013 is a reply to message #263004] Tue, 28 August 2007 14:34 Go to previous messageGo to next message
pablolee
Messages: 2835
Registered: May 2007
Location: Scotland
Senior Member
You might want to get your clauses in the right order too

SELECT
FROM
WHERE
Re: & sign in where clause [message #263014 is a reply to message #263010] Tue, 28 August 2007 14:34 Go to previous messageGo to next message
kilyas
Messages: 24
Registered: March 2007
Junior Member
is there something I can do as a part of this very query?
Re: & sign in where clause [message #263020 is a reply to message #263014] Tue, 28 August 2007 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
kilyas wrote on Tue, 28 August 2007 21:34
is there something I can do as a part of this very query?

Can you precise what you mean?

Regards
Michel

[Updated on: Tue, 28 August 2007 14:41]

Report message to a moderator

Re: & sign in where clause [message #263026 is a reply to message #263004] Tue, 28 August 2007 15:08 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
That feature is tool dependent. For example, in SQL*Plus you can turn the feature off as Michel indicates or change the character to something else
SET DEFINE @

If you do not want to manipulate the tool and want to avoid the prompt for a single query, do not reference the & character, use the CHR function instead. For example:

SELECT 'S'||CHR(38)||'P400'
FROM dual;

'S'||C
------
S&P400
Re: & sign in where clause [message #263070 is a reply to message #263026] Wed, 29 August 2007 00:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If you do not want to manipulate the tool and want to avoid the prompt for a single query, do not reference the & character, use the CHR function instead

What if you change your define character? Do you have to change all character strings in chr concatenation in case of your client define another character or in case you don't a priori know which string will be given? (I assume the string is not a constant one)

Regards
Michel
Re: & sign in where clause [message #263082 is a reply to message #263070] Wed, 29 August 2007 00:56 Go to previous messageGo to next message
cmerry
Messages: 109
Registered: November 2005
Location: Idaho
Senior Member
Sorry, I don't understand your comment/question

Changing the DEFINE character will have no impact on the use of CHR
Re: & sign in where clause [message #263084 is a reply to message #263004] Wed, 29 August 2007 00:56 Go to previous messageGo to next message
thani_oracle
Messages: 44
Registered: August 2007
Location: Bangalore
Member

Hi
I can't understand this

SELECT *
WHERE short_nm = 'S&P400'
FROM options

Why he's Writing where clause before from clause...
Any one explain me this ....

Regards,
Thani....

Re: & sign in where clause [message #263086 is a reply to message #263084] Wed, 29 August 2007 00:59 Go to previous message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It's just a wrong syntax.
See pablolee's answer above.

Regards
Michel
Previous Topic: errors login at SQLPLUS
Next Topic: Free tool to browse a database? execute queries? create PL/SQL packages? etc?
Goto Forum:
  


Current Time: Thu Dec 08 12:46:20 CST 2016

Total time taken to generate the page: 0.15555 seconds