syntax error when trying to call JPA function with custom query

Question

Inside my Repository, i have following

public interface TenantRepository extends JpaRepository<Tenant, UUID> {
    ...

    @Query("select substring(email from '@(.*)$') as domain from Tenant group by domain")
    public List<String> findAllDomain();
}

When i tried to excute it, i got an error which says:

Syntax error in SQL statement "select substring(tenant0_.email, [*]from, '@(.*)ParseError: KaTeX parse error: Can't use function '\)' in math mode at position 2: '\̲)̲&nbsp;as&nbsp;c…') as col_0_0_ from tenants tenant0_ group by col_0_0_

So i change the query to this:

"select substring(email from '@(.*)\\$') as domain from Tenant group by domain"

But it got another error:

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select substring(tenant0_.email, [*]from, '@(.*)\\$') as col_0_0_ from tenants tenant0_ group by col_0_0_"; expected "INTERSECTS (, NOT, EXISTS, UNIQUE, INTERSECTS"; SQL statement:
select substring(tenant0_.email, from, '@(.*)\$') as col_0_0_ from tenants tenant0_ group by col_0_0_

When i try the query on my PgAdmin4, it works.

Any suggestions?

thx.

Your substring() method has SQL keywords in it. Lookup JPA substring() function or whatever DB it is for use of substring function syntax requirements. — Samuel Marchant
– Samuel Marchant, Commented Sep 13, 2022 at 4:59

johnnyutts · Accepted Answer · 2022-09-13 05:19:46Z

1

I assume you are trying to get all the email domains. In JPA you can use combination of locate and substring functions to achieve this

    @Query("select substring(t.email, locate('@', t.email) + 1) as domain from Tenant t group by domain")
    public List<String> findAllDomain();

emails in db

[email protected]
[email protected]
[email protected]

query response

domain1.com, domain2.com

edited Sep 13, 2022 at 5:19

answered Sep 13, 2022 at 5:07

johnnyutts

1,4521 gold badge9 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

syntax error when trying to call JPA function with custom query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related