syntax error when trying to call JPA function with custom query-CodePudding

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.

CodePudding user response：

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