Forum Stats

  • 3,760,215 Users
  • 2,251,665 Discussions
  • 7,871,023 Comments

Discussions

SQL Developer, SQL Server and the ampersand

573865
573865 Member Posts: 20
edited Aug 14, 2012 9:41AM in SQL Developer
Hello,

I am connected to a SQL Server database (2008) and am having difficulty getting a query to run properly in SQL Developer (3.1.07).

Simplified Example

select table_field
from table
where table_field <> 'This & That';

Expected results

table_field
Other stuff
Other stuff
Other stuff

Actual results

table_field
Other stuff
This & That
This & That
....


I cannot seem to get SQL Developer to recognize the & as a literal ampersand. I've tried escaping it numerous ways, but it always thinks it is a variable.

The query works exactly as expected in MS SQL Management Studio and Toad. Of course, I can just run it in one of those but I'd like to try to get this to work in SQL Developer. Suggestions?

Please let me know if I can provide further information. Thanks.
Tagged:

Best Answer

  • Dermot ONeill-Oracle
    Dermot ONeill-Oracle Member Posts: 618 Employee
    Accepted Answer
    Hi,

    SQL Developers worksheet is designed for Oracle, but we allow you to run statements against SQL Server and other non Oracle databases.
    This can lead to some quirks, like the one you experienced.

    Oracles SQL*Plus uses ampersand to define substitution variables.
    Oracle SQL Developer has implemented the same concept.
    Unfortunately this does not make sense when running something against a non Oracle database.

    As a workaround, you can run the following SQL*Plus command in SQL Developer, before your SQL Server query.
    SET DEFINE OFF;
    select 'hello & goodbye';


    The following maybe of some help as well.
    http://dermotoneill.blogspot.com/2010/11/workheet-hints.html
    http://dermotoneill.blogspot.com/2010/12/sql-server-and-sybase-browsing.html

    Regards,
    Dermot.
    SQL Developer Team.

Answers

  • Dermot ONeill-Oracle
    Dermot ONeill-Oracle Member Posts: 618 Employee
    Accepted Answer
    Hi,

    SQL Developers worksheet is designed for Oracle, but we allow you to run statements against SQL Server and other non Oracle databases.
    This can lead to some quirks, like the one you experienced.

    Oracles SQL*Plus uses ampersand to define substitution variables.
    Oracle SQL Developer has implemented the same concept.
    Unfortunately this does not make sense when running something against a non Oracle database.

    As a workaround, you can run the following SQL*Plus command in SQL Developer, before your SQL Server query.
    SET DEFINE OFF;
    select 'hello & goodbye';


    The following maybe of some help as well.
    http://dermotoneill.blogspot.com/2010/11/workheet-hints.html
    http://dermotoneill.blogspot.com/2010/12/sql-server-and-sybase-browsing.html

    Regards,
    Dermot.
    SQL Developer Team.
  • 573865
    573865 Member Posts: 20
    Thank you for the prompt response.
This discussion has been closed.