1

Here is the senerio,I am connected to a SQL Server database (2012) using Oralce SQl Developer(4.1.3).here is the sql statement i plan to execute.

    select   FORMAT(createDate,'yyyyMMdd') AS date,
         SUM(bb_cnt)                   AS 'Broadband Service',
         SUM(tel_cnt)                  AS 'Telephone Service',
         SUM(bb_tel_cnt)               AS 'Broadband & Telephone Service',
from    (select createDate,
                CASE WHEN ServicePlan LIKE '%Broadband Service%'              then 1 else 0 end AS bb_cnt,
                CASE WHEN ServicePlan LIKE 'Telephone Service%' OR ServicePlan LIKE '%, Telephone Service%' then 1 else 0 end AS tel_cnt,
                CASE WHEN ServicePlan LIKE '%Broadband & Telephone Service%' then 1 else 0 end AS bb_tel_cnt,
         from   EFormRegisterationData
        ) a
group by FORMAT(createDate,'yyyyMMdd') order by 1;

the problem is 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,I google the question and finds out this useful link, SQL Developer, SQL Server and the ampersand,in this article,SQL Developer Team mentioned the walkaround," before your SQL Server query. SET DEFINE OFF; ",I tried,but the SQL Developer tells it has syntax error,I'm at a loss,any suggestion will be appreciated,thanks.

plus:exact the same sql works fine when using DataGrip connected to the SQL Server 2012,it's so werid, so I think the problem is client software,not the sql statement itself.

sheldon shen
  • 35
  • 7
  • I found this already answered at this link: [http://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string](http://stackoverflow.com/questions/12961215/escaping-ampersand-character-in-sql-string) – Anjani Kumar Agrawal Jul 20 '16 at 17:58
  • @Anjani Kumar Agrawal.I tried the solutions provided in this thread,it does work fine in other context,but they all are invalid in my context,using SQL Developer connected to the SQL Server 2012,the environment is different. – sheldon shen Jul 21 '16 at 00:57

1 Answers1

0

Try breaking it up. I'm on my mobile so can't test other solutions. 

ServicePlan LIKE 'Broadband%' AND ServicePlan LIKE '%Telephone Service' THEN ...

http://rextester.com/ZFWCG68878

S3S
  • 24,809
  • 5
  • 26
  • 45
  • ,there are two "&" in the sql statement( AS 'Broadband & Telephone Service',LIKE '%Broadband & Telephone Service%'),they all need to escaped,what i want is the & character be escaped,not ignored,anyway,thanks again. – sheldon shen Jul 20 '16 at 04:32
  • @sheldonshen where is that statement at in your code? See my updated answer for an example of this code running fine on SQL Server without escaping. – S3S Jul 20 '16 at 13:53
  • 1
    ,it does work fine,and I also find out,exact the same sql works fine when using DataGrip connected to the SQL Server 2012,it's so werid, so I think the problem is client software(SQL Developer),not the sql statement itself. – sheldon shen Jul 21 '16 at 01:18