Hibernate query to remove leading zero

[Tiya Khambadkone]

I want to do :

 select * from accounts where account_number = '234567'

User can enter on the GUI as account number = '234567' but database column could have the value of '0000234567' . Still it should match.

This SQL query does not work :

select * from accounts a where 
(:accountNumber LIKE (TRIM(REPLACE((a.account_number), '0', '%'))))

The following Oracle query works :

select ltrim(to_char(account_number), '0') from accounts 

Can somebody suggest me a HQL query ?

[Roel De Nijs]

Tiya Khambadkone wrote:User can enter on the GUI as account number = '234567' but database column could have the value of '0000234567' . Still it should match.

Does the account number in the database column always have leading zeroes when its length is less than 10? Or could the account number in the database be 234567, 0234567, 00234567, 000234567 or 0000234567?

[Tiya Khambadkone]

account_number in database is a VARCHAR(40) and that may or may not contain leading zeros. Also how many leading zeros is again not known. It could be '000123' or '00123' or just '123'

[Roel De Nijs]

Also posted on SO.

Please BeForthrightWhenCrossPostingToOtherSites! Many people (including me) don't like to waste their time and effort when your question was already answered on another site.

[Tiya Khambadkone]

The question is also posted on SO. However I am not getting any answer so I posted here.
Isn't it obvious that one could seek answer on another community if not getting from one community.
Please let me know if it is a sin to post questions on 2 websites?
I thought the whole purpose of SO or Code Ranch is to help people and the IT community and grow knowledge.
It should not matter which community answers questions.

I would have been glad if you would have helped me with the issue and then lectured about code ranch guidelines.

[Roel De Nijs]

Tiya Khambadkone wrote:account_number in database is a VARCHAR(40) and that may or may not contain leading zeros. Also how many leading zeros is again not known.

Based on the JPQL Supported functions section from the excellent Java Persistence WikiBook, you probably need the TRIM function to remove the leading zeros. Something like this might do the jobselect a from accounts a where :accountNumber = TRIM(LEADING '0' FROM a.account_number)

Hope it helps!
Kind regards,
Roel

[Roel De Nijs]

Tiya Khambadkone wrote:Isn't it obvious that one could seek answer on another community if not getting from one community.
Please let me know if it is a sin to post questions on 2 websites?

I never said you can't post questions to more than one website. I only said you should be honest about it and just mention in your first post that you have posted the same question on SO (or any other website).

Tiya Khambadkone wrote:I thought the whole purpose of SO or Code Ranch is to help people and the IT community and grow knowledge.
It should not matter which community answers questions.

True indeed! But maybe I spend two hours of my (precious) spare time to help you to find an answer to your question. And then it appears the question was already answered 5 hours ago so I wasted my spare time (and I could have helped other ranchers with unanswered questions). If you would have added a link to the question on the other website, I could easily see by a simple click if your question is still unanswered (and decide to spend some time on it).

Tiya Khambadkone wrote:I would have been glad if you would have helped me with the issue and then lectured about code ranch guidelines.

I already helped you by asking a follow-up question (although you probably don't see it this way), so it becomes crystal-clear for other ranchers (with more Hibernate knowledge than me) to see what you are looking for as this was not completely obvious from your initial post.

[Tiya Khambadkone]

Sure. Next time if I come across such situation then I will keep in my mind to post the link from other website.

Thank you for your response and trying to help me out.

When I try your query , I get :

org.springframework.dao.InvalidDataAccessResourceUsageException: could not prepare statement; SQL [select * from accounts where (? LIKE (TRIM(LEADING '0' FROM account_number)))]; nested exception is org.hibernate.exception.SQLGrammarException: could not prepare statement
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.convertHibernateAccessException(HibernateJpaDialect.java:231)
at org.springframework.orm.jpa.vendor.HibernateJpaDialect.translateExceptionIfPossible(HibernateJpaDialect.java:214)

[Roel De Nijs]

Can you provide the relevant code snippet?

[Tiya Khambadkone]

@Repository
@Transactional(propagation = Propagation.REQUIRED)
public interface AccountsDAO extends JpaRepository<Accounts, Long> {

	@Query(value = "select * from accounts where (:accountNumber LIKE (TRIM(LEADING '0' FROM account_number)))", nativeQuery = true)
	List<Accounts> findaAccounts(@Param("accountNumber") String accountNumber);
}

[Roel De Nijs]

So you ask for a HQL (or JPQL) query in your original post. And then you define it as a native query... I can understand why you get a runtime exception.

You should either use the JPQL version (and then you can use the provided TRIM function as it's JPQL syntax)select a from Accounts a where :accountNumber = TRIM(LEADING '0' FROM a.account_number)or use the native version of this query but then you'll need to look for the equivalent of the provided TRIM function (as you have to use the appropriate syntax of the database you are using).

Note: I guess account_number is the database column name, so if you decide to use the JPQL version you should replace account_number in the query with the appropriate field from the Accounts class.

[Dave Tolls]

Tiya Khambadkone wrote:account_number in database is a VARCHAR(40) and that may or may not contain leading zeros. Also how many leading zeros is again not known. It could be '000123' or '00123' or just '123'

Ow.
Definitely "ow".

This sort of poor data quality really annoys me.

[Winston Gutkowski]

Tiya Khambadkone wrote:account_number in database is a VARCHAR(40) and that may or may not contain leading zeros. Also how many leading zeros is again not known. It could be '000123' or '00123' or just '123'

Ooof.

<DBA hat on>
That just sounds plain WRONG, because it means that either:
(a) Account "00123" is not the same thing as account "000123" - or indeed "123".
(b) Something (possibly a trigger) is already having to check whether the table contains a "trimmed" version of the account number to maintain consistency.

I was trying to find a phrase like "violates first normal form", but TBH it's so horrible that I don't think it's even definable in terms of keys and attributes, because account number doesn't even qualify as a key.
</DBA hat on>

Winston

[Tiya Khambadkone]

I am still trying to figure out how to do it .
Anyways, the requirement is : The account number of a person could be '00001234567'. however if a person on GUI enters an account number as 1234567 then it should be considered a valid number after matching it with the database data.
Why will this be a bad database structure design ?

[Roel De Nijs]

Tiya Khambadkone wrote:Why will this be a bad database structure design ?

Because after 3 days you are still trying to figure out how to do it And for the reasons mentioned by Winston as well of course.

Let's assume all account numbers have a length of 10 and leading zeros are added if the length of the account number is less than 10 (e.g. 1234). Then you could use this simple code in your query to get the account number from the table

int accountNumber = 1234; // entered in GUI
String parameter = String.format("%010d", accountNumber);

So your work would be done in less than 5 minutes, instead of struggling for more than 3 days... Now you only have to care about one account number instead of 3 or 4 (or maybe even 5) different representiations of the same account number ("1234", "01234", "0001234",...).

[Tiya Khambadkone]

Unfortunately that is not the case.
The account number is alpha-numeric . so it makes sense to define it as varchar(40). The length is not certain. It could be 'H122' or '0000012345'.
I need to trim the padded zeroes if they are present while fetching from the database.

[Winston Gutkowski]

Tiya Khambadkone wrote:The account number is alpha-numeric . so it makes sense to define it as varchar(40).

Seems reasonable...

The length is not certain. It could be 'H122' or '0000012345'.

...but that is NOT.

I need to trim the padded zeroes if they are present while fetching from the database.

Sounds to me like you're guessing here.

If 'account number' is a unique identifier, and is defined the way you say it is, then something, somewhere, in the database must be "normalizing" it to determine whether it already exists; otherwise you would be able to add "123" and "0123" and they would show up as different accounts.

So, my suggestion would be to find that code, work out what IT does, and then either replicate it in your code or call the database function yourself (assuming it's defined as one) - the latter being the safer option.

That's after you shoot your database designer, of course.

Winston

[Roel De Nijs]

Tiya Khambadkone wrote:The account number is alpha-numeric . so it makes sense to define it as varchar(40). The length is not certain. It could be 'H122' or '0000012345'.
I need to trim the padded zeroes if they are present while fetching from the database.

Just for the record: the criticism is not about the column being varchar(40) because that's just fine. It's only about the contents of this column: '000123' or '00123' or just '123' could refer to the same account number. That's poor data quality! Because you would assume account_number to have a unique constraint as each number should refer to a unique account. But even with a unique constraint you could insert 3 records with different account numbers (e.g. '000123', '00123' and '123') but each record is "identifying" the same account number...

[Tiya Khambadkone]

oh. you misundersstood !
I might not be clear while explaining .

the account_number couldbe '000012345' or '00012345' or '12345' but this all should refer to the same row. It is unique. They can not co-exist in the database table.

[Dave Tolls]

Tiya Khambadkone wrote:
the account_number couldbe '000012345' or '00012345' or '12345' but this all should refer to the same row. It is unique. They can not co-exist in the database table.

So how are the stored in the database?

What does the code that is storing this stuff do to ensure that those 3 unequal Strings are treated as the same so that it doesn't accidentally add '00012345' and '12345' into the table.
The database isn't going to be handling that without some code.

[Winston Gutkowski]

Tiya Khambadkone wrote:oh. you misundersstood !
I might not be clear while explaining .
the account_number could be '000012345' or '00012345' or '12345' but this all should refer to the same row. It is unique. They can not co-exist in the database table.

No, I think you explained it very well. It's how your 'account number' is defined and what it can contain that we're taking issue with.

First: Using a varchar for a unique key at all is pretty "iffy".

Second: You plainly have more than one format of "number" that you're storing. Again, not great.

Third: You still haven't told us how the database works out whether an account number exists or not. And THAT'S what you need to know.

Now if it simply leaves it up to whatever program is adding rows to it to to decide, then your designer should be disembowelled before he's shot, because that's just plain nuts.

Otherwise, you need a definitive explanation of what the database does to ensure integrity - and, as I said above, you should probably use ITS function or procedure, rather than "rolling your own equivalent".

Winston

[Tiya Khambadkone]

I have decided to go with native sql query :

select ltrim(account_number, '0')
from accounts
where account_number like '%12345'


This ORACLE query works as expected on SQL developer but when i try to run it using hibernate... it complains :

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ,

[Tiya Khambadkone]

instead of ltrim if I use LEADING '0' , it works

[Roel De Nijs]

Tiya Khambadkone wrote:instead of ltrim if I use LEADING '0' , it works

Glad to hear it's working!

So how does the findaAccounts() method in the AccountsDAO interface look now? Might be helpful for other ranchers.

[Winston Gutkowski]

Tiya Khambadkone wrote:I have decided to go with native sql query :

select ltrim(account_number, '0')

This ORACLE query works as expected on SQL developer but when i try to run it using hibernate... it complains :

Caused by: java.sql.SQLSyntaxErrorException: unexpected token: ,

Well the only place you have a ',' is in
  select ltrim(account_number, '0')
which suggests to me that it doesn't like the clause.

Have you tried:
select account_number
from accounts
where ltrim(account_number, '0') like '%12345'
because that, to me, is logically what you want - assuming the like argument is correct (??).
Your query isn't.

But the fact is that we STILL don't know whether this is the correct way to get your data or not. Have you checked what the database does?

Winston

[Tiya Khambadkone]

I have posted the code snippet earlier.

I did not go into much detail for now because I have already spent a lot of time on this and need to get other stuff working to meet my deadline. i will revisit this later.
For now, I am worrying about if the TRIM should be used at database level or java level ? which is more expensive? any thoughts?

[Dave Tolls]

Tiya Khambadkone wrote:
For now, I am worrying about if the TRIM should be used at database level or java level ? which is more expensive? any thoughts?

The general rule is "do any data filtering in the database". It's what it was built for.
There may be exceptions, but you'll only find them when you've attempted to do it in the database.

However, as I said earlier, anything involving removing leading stuff from a varchar column will probably result in full table scans being required, unless you have some clever indexing going on.

Also, you haven't told us if you'd found out how the code that inserts this data ensures that '00012345' and '12345' are not both inserted into the database.

[Tiya Khambadkone]

My query did not work as expected. grrrrrrrrrrrr


The account table has account_number column with values such as '00012345','0012345','12345','54564654','2343433'. the length of column is varchar40

SELECT TRIM (LEADING '0' FROM a.account_number) from accounts a where a.account_number like '%12345';

This query gives me 3 rows

However when I do
select * from Accounts acc
where acc.account_number IN (SELECT TRIM (LEADING '0' FROM a.account_number) from accounts a where a.account_number like '%12345');

This query gives me 1 row. I need 3 rows. How can I get the 2nd query return 3 rows?

[Roel De Nijs]

Tiya Khambadkone wrote:The account table has account_number column with values such as '00012345','0012345','12345','54564654','2343433'. the length of column is varchar40

This statement completely contradicts with a previous statement you posted a week ago

Tiya Khambadkone wrote:the account_number couldbe '000012345' or '00012345' or '12345' but this all should refer to the same row. It is unique. They can not co-exist in the database table.

So it seems you don't have a clue yourself about what's considered to be normal (and expected) behavior. And that makes it for others unfortunately impossible to help you...

[Tiya Khambadkone]

Yes. my mistake that I did not understand the requirement clearly.

[Tiya Khambadkone]

I made it work like this:

select * from Accounts acc
where acc.account_id IN (SELECT a.account_id from accounts a where TRIM (LEADING '0' FROM a.account_number) = '12345');

this query gives me 3 rows.

Hopefully my further testing won't have any issues.

[Roel De Nijs]

Tiya Khambadkone wrote:I made it work like this:

Thanks for coming back and sharing your solution. Might be helpful for other ranchers as well. Have a cow!

[Tiya Khambadkone]

Thank you for the cow !

[Mark Spritzler]

I know this has been solved for the OP. But there is another possible solution, not in the query, but in terms of removing an unknown number of leading "0" and returning what is left, and then being able to use it in JPA queries after. It is posted in the SO post linked at the top, so I can't take credit for it, but that one did have one slight error though in that the result of the formula is a String not an Integer.

@Formula("TRIM(LEADING '0' FROM account_number)")
private String trimmedAccountNumber;

Thanks

Mark