CODEWITHSUNDEEP
sqloracle-databasejdbc
Rajasekhar
Rajasekhar

Reputation: 807

Query to handle single quoted objects in sql

I was using a sql query in jdbc

SELECT COUNT (COST_CENTER) AS count FROM IMDB1_FINANCE_BUDGET where COST_CENTER='object name'

this is working fine but i have some test objects like '0654603 ? SSG Accounting with a single quote in beginning now query will be

SELECT COUNT (COST_CENTER) AS count FROM IMDB1_FINANCE_BUDGET where COST_CENTER=''0654603 ? SSG '

now it is throwing exception

java.sql.SQLException: ORA-00933: SQL command not properly ended

how to handle such type of objects

Upvotes: 0

Views: 45

Answers (1)

Lalit Kumar B
Lalit Kumar B

Reputation: 49062

The best way to deal with single-quotations in such cases is to use Quoting string literal technique

For example, q'['SCOTT]'

SQL> WITH DATA AS(
  2  SELECT '''SCOTT' nm FROM dual
  3  )
  4  SELECT * FROM DATA WHERE nm = q'['SCOTT]';

NM
------
'SCOTT

SQL>

Of course, the traditional way to enclose it with single-quotes '''SCOTT' would still work -

SQL> WITH DATA AS(
  2  SELECT '''SCOTT' nm FROM dual
  3  )
  4  SELECT * FROM DATA WHERE nm = '''SCOTT';

NM
------
'SCOTT

SQL>

Upvotes: 2

Related Questions