OraFAQ Forum: Client Tools » & sign in where clause

Home » Other » Client Tools » & sign in where clause

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

& sign in where clause [message #263004]

Tue, 28 August 2007 14:11

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?

Report message to a moderator

Re: & sign in where clause [message #263010 is a reply to message #263004]

Tue, 28 August 2007 14:27

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

Senior Member
Account Moderator

Many solutions.
Here's one: "set define off"

Regards
Michel

Report message to a moderator

Re: & sign in where clause [message #263013 is a reply to message #263004]

Tue, 28 August 2007 14:34

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

You might want to get your clauses in the right order too

SELECT
FROM
WHERE

Report message to a moderator

Re: & sign in where clause [message #263014 is a reply to message #263010]

Tue, 28 August 2007 14:34

kilyas
Messages: 24
Registered: March 2007

Junior Member

is there something I can do as a part of this very query?

Report message to a moderator

Re: & sign in where clause [message #263020 is a reply to message #263014]

Tue, 28 August 2007 14:40

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

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

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

Report message to a moderator

Re: & sign in where clause [message #263070 is a reply to message #263026]

Wed, 29 August 2007 00:33

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

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

Report message to a moderator

Re: & sign in where clause [message #263082 is a reply to message #263070]

Wed, 29 August 2007 00:56

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

Report message to a moderator

Re: & sign in where clause [message #263084 is a reply to message #263004]

Wed, 29 August 2007 00:56

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....

Report message to a moderator

Re: & sign in where clause [message #263086 is a reply to message #263084]

Wed, 29 August 2007 00:59

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

Senior Member
Account Moderator

It's just a wrong syntax.
See pablolee's answer above.

Regards
Michel

Report message to a moderator

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

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Jan 19 00:51:36 CST 2026