title: JPA, Hibernate and Spring Data: convert strings to numbers in the where clause on MS SQL server
body: <p>I am doing a web project. It uses Spring (5.0.2.RELEASE), Hibernate (5.2.12.Final), and Spring Data (2.0.2.RELEASE). These are the latest versions and so JPA 2.1 is available to me.  The website runs on Windows and MS SQL Server 2014 Enterprise. </p>
  
  <p>I have a class that has nvarchar field</p>
  
  <pre><code>Class1 {
      ....
      @Column(columnDefinition = "NVARCHAR(20)")
      private String textOrNumber = null;
      ...
  }
  </code></pre>
  
  <p>I am able to run the following native query against the database successfully in MS SQL Server Management Studio (A Windows program that has nothing to do with Java) . </p>
  
  <pre><code>SELECT c.textOrNumber FROM Class1 c WHERE ISNUMERIC(c.textOrNumber ) = 1 
     AND CAST(c.textOrNumber AS DECIMAL(17,2)) &gt; 0
  </code></pre>
  
  <p>The above query is able to find records whose values on <code>textOrNumber</code> are numbers (<strong>integers or decimals</strong>) greater than 0. </p>
  
  <p>Here is what I have in Java:</p>
  
  <pre><code>Query q = em.createQuery("SELECT c FROM Class1 c WHERE ISNUMERIC(c.textOrNumber ) = 1 "
  + "AND CAST(c.textOrNumber AS DECIMAL(17,2))");
  return q.getResultList();
  </code></pre>
  
  <p>Here is the error message:</p>
  
  <pre><code>Caused by: 
  org.hibernate.hql.internal.ast.QuerySyntaxException: expecting CLOSE, found '(' near line 1, column 122 [SELECT c FROM mytest.Class1 c WHERE ISNUMERIC(c.textOrNumber) = 1 AND CAST(c.textOrNumber AS DECIMAL(17,2)) &gt; 2]
      at org.hibernate.hql.internal.ast.QuerySyntaxException.convert(QuerySyntaxException.java:74)
      at org.hibernate.hql.internal.ast.ErrorCounter.throwQueryException(ErrorCounter.java:91)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:291)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:186)
      at org.hibernate.hql.internal.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:141)
      at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:115)
      at org.hibernate.engine.query.spi.HQLQueryPlan.&lt;init&gt;(HQLQueryPlan.java:77)
      at org.hibernate.engine.query.spi.QueryPlanCache.getHQLQueryPlan(QueryPlanCache.java:153)
      at org.hibernate.internal.AbstractSharedSessionContract.getQueryPlan(AbstractSharedSessionContract.java:553)
      at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:662)
      at org.hibernate.internal.AbstractSessionImpl.createQuery(AbstractSessionImpl.java:23)
      at sun.reflect.GeneratedMethodAccessor37.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      at java.lang.reflect.Method.invoke(Method.java:498)
  </code></pre>
  
  <p>How can I fix this by using <code>CAST</code> or something else?</p>
  
  <p><strong>Additional info:</strong> </p>
  
  <p>This runs in JPA without any problem (it finds records with numbers on the textOrNumber field):</p>
  
  <pre><code>SELECT c FROM Class1 c WHERE ISNUMERIC(c.textOrNumber ) = 1  
  </code></pre>