CODEWITHSUNDEEP
javasqlspringoracle-databasejpa
Man
Man

Reputation: 53

TRIM replaces LTRIM/RTRIM

I am fairly new to Oracle.

Is it safe to say that LTRIM(RTRIM(<myVarchar>)) is totally replaceable by TRIM(<myVarchar>) if I want to replace both leading and trailing whitespaces in Oracle 11g?

Also, when I am trying to use this function in my query using JPA, I am getting error "org.hibernate.hql.internal.ast.QuerySyntaxException: unexpected AST node".

Here is the query that I am using:

@Query("Select p from OldPin p WHERE TRIM(p.eeNo) = :accNum and 
TRIM(p.pinStatus) = 'A' and TRIM(p.memberType='E') and TRIM(p.sCode) in 
('MSHK','MCMG')")
    public OldPin findByAccountNum(@Param("accNum") String accNum);

Upvotes: 0

Views: 1558

Answers (2)

Mario Ruiz
Mario Ruiz

Reputation: 36

Too, you can use 

select '%'||replace(' hello world ', ' ' , '')||'%' from dual;

and the output is

%helloworld%

Upvotes: 1

Gaj
Gaj

Reputation: 886

Trim will remove both leading and trailing spaces by default eg. Trim(' test ') output will be test

If we use Trim(both from ) then it will remove a character from both side eg., Trim(both '1' from '111oracle111') output will be oracle Trim(leading '1' from '111oracle111') output will be oracle111 Trim(trailing '1' from '111oracle111') output will be 111oracle

Trim(both 'ab' from 'abtechab') - it will throw error, because trim will support single character only

In RTrim and LTrim we can remove any number of character.

Upvotes: 1

Related Questions