JPA Integer conversion to String for MySQL query

mysql order by string contains number
mysql cast
how to show string as number ascending in mysql
how to sort varchar as number in mysql
jpa concat example
jpa query case insensitive
sql convert string to boolean
sql-order by numeric varchar

I have to write a JPA query to select data in a MySQL DB.

I have this part of code:

(e.fgpVndrNumber, e.companyName1, e.vndrCountry,, e.postalCode, e.street, e.vatRegistrNum, 
e.vendorTypeDesc, e.companyName2, e.taxNumber2, 
e.taxNumber1, e.organizationCode) 
FROM ScVendor e 

I want to add this where clause:

WHERE str(e.fgpVndrNumber) LIKE '%123%' 


to_char(e.fgpVndrNumber) LIKE '%123%'

In few words, I have to convert an Integer in String.

But I obtain an error.



This is the complete Java code:

String selAndForm = "SELECT NEW, e.companyName1, e.vndrCountry,"
                + ", e.postalCode, e.street, e.vatRegistrNum, e.vendorTypeDesc, e.companyName2, e.taxNumber2, "
                + "e.taxNumber1, e.organizationCode)  FROM ScVendor e ";

        String where = null;

        if (isNotNull(companyName)) {
            if (searchCriteriaCompName != null) {
                if (searchCriteriaCompName.equals("contains")) {
                    companyName = "%" + companyName + "%";
                if (searchCriteriaCompName.equals("startWith")) {
                    companyName = companyName + "%";
                if (searchCriteriaCompName.equals("endWith")) {
                    companyName = "%" + companyName;

            if (where == null) {
                where = "WHERE lower(e.companyName1) like '" + companyName.toLowerCase() + "'";
            } else {
                where = where + " AND lower(e.companyName1) like '" + companyName.toLowerCase() + "'";

        if (isNotNull(immediateParent)) {
            if (searchCriteriaParentCode != null) {
                if (searchCriteriaParentCode.equals("contains")) {
                    immediateParent = "%" + immediateParent + "%";
                if (searchCriteriaParentCode.equals("startWith")) {
                    immediateParent = immediateParent + "%";
                if (searchCriteriaParentCode.equals("endWith")) {
                    immediateParent = "%" + immediateParent;
                if (searchCriteriaParentCode.equals("equals")) {
                    // do nothing

            if (where == null) {
                where = "WHERE e.immediateParent LIKE '" + immediateParent + "'";
            } else {
                where = where + " AND e.immediateParent LIKE '" + immediateParent + "'";

        if (isNotNull(supplierCode)) {

            logger.debugT("supplierCode=" + supplierCode);

            if (searchCriteriaParentCode != null) {
                if (searchCriteriaSuppCode.equals("contains")) {
                    supplierCode = "%" + supplierCode + "%";
                if (searchCriteriaSuppCode.equals("startWith")) {
                    supplierCode = supplierCode + "%";
                if (searchCriteriaSuppCode.equals("endWith")) {
                    supplierCode = "%" + supplierCode;
                if (searchCriteriaSuppCode.equals("equals")) {
                    // do nothing

            if (where == null) {
                where = "WHERE e.fgpVndrNumber.toString() like '" + supplierCode + "'";
            } else {
                where = where + " AND e.fgpVndrNumber.toString() like '" + supplierCode + "'";

        StringBuffer SQL = new StringBuffer();

        SQL.append(" AND e.deletedAt IS NULL");

        Query query = eManager.createQuery(SQL.toString());

fgpVendorNumber is an Integer. I want to convert it in a String.

No explicit casting is required to search part of the number.

Following query format should be working with no syntax or logical errors.

select ...
  from ...
 where e.fgpVndrNumber like '%123%'

SQL order string as number, select col from yourtable order by cast(col as unsigned) BTW MySQL converts strings from left to right. Examples: string value | integer value after conversion (​For people who use JPA 2.0, where no casting is allowed) JPA Integer conversion to String for MySQL query. Tag: java,mysql,sql,jpa. I have to write a JPA query to select data in a MySQL DB. I have this part of code:

Use cast function in MySql Databases.

WHERE cast(e.fgpVndrNumber as varchar) LIKE '%123%'

See also

MySql Cast

Strings in JPA JPQL/Criteria Queries (LIKE, LOWER, UPPER , Explains string expressions in JPQL queries, including LIKE, LOCATE, LOWER, (str) function returns the number of characters in the argument string as an int . The LOWER(str) and UPPER(str) functions return a string after conversion to See a comparison of Hibernate, EclipseLink, OpenJPA, DataNucleus, MySQL,  Unfortunately, concat is not available till SQLServer 2012. However, in the link you sent I saw that while 2 + 'string' doesn't work, ''+2+'string' does work. Since your link to the other question yielded this hint and the int/string conversion was my specific question I'll select your answer as the solution. Thanks.

Trying to compare an Integer against a String in JPA JPQL would cause an error. I was able to convert the Integer into a String that could be compared doing this:

WHERE substring(xInteger, 0) like '%stringFilter%'

Numeric strings: VARCHAR fields holding numbers, Numeric strings are numbers that are stored in text columns. yield an error (e.g. PostgreSQL) many databases just add an implicit type conversion. SELECT . Questions: I am using JPA in my project. I came to a query in which I need to make join operation on five tables. So I created a native query which returns five fields. Now I want to convert the result object to java POJO class which contains the same five Strings. Is there any

I was able to use TRIM with my JPA query:

WHERE TRIM(e.fgpVndrNumber as varchar) LIKE '%123%'

How to cast an integer to a boolean in a MySQL SELECT clause , UNSIGNED [INTEGER]. Since BOOLEAN is not in this list, it can never be returned by CAST or CONVERT. You could use the IF function to generate strings Spring Data JPA provides built-in methods for your basic CRUD operations but as we are going to create custom query or native query so we will build query in our own way. To write a repository we need to create an interface that will extend JpaRepository .

How to Use MySQL Conversion Functions, MySQL Conversion Functions convert a value from one data type to another. Conversions can be SIGNED [INTEGER]. Converts an unsigned integer to a signed integer. This query converts a string to a DATETIME data type. The result is  Converts value to CHAR (a fixed length string) SIGNED: Converts value to SIGNED (a signed 64-bit integer) UNSIGNED: Converts value to UNSIGNED (an unsigned 64-bit integer) BINARY: Converts value to BINARY (a binary string)

MySQL :: MySQL Connector/J 5.1 Developer Guide :: 5.5 Java , String , and any numeric type can be converted to any of the Java numeric types, although type that has less precision or capacity than the MySQL data type you are converting to/from. Integer (regardless of whether it is UNSIGNED or not). Conversion from String. Alternatively, we could convert from a String value as well. Consider that we get the query parameter as a String: DateTimeFormatter dateTimeFormatter = DateTimeFormatter.ofPattern( "dd-MM-YYYY" ); String dateString = dateTimeFormatter.format( );

JPA Attribute Converters, Take a look at mapping JDBC types to Java classes in JPA using attribute converters. In our case, we'll convert the attribute to a String value that contains converter by writing a query that retrieves the whole Person class:. In this blog we will see how to Convert Integer to String in SQL Server. Write the following script in SQL Server. We have three different ways in which we can achieve the desired tasks. We would be examining each one of them and check their output. In the first method we have used CAST function; in the second method we have used CONVERT