Comparing Strings with trailing spaces in SQL statements

classic Classic list List threaded Threaded
4 messages Options
iostream iostream
Reply | Threaded
Open this post in threaded view
|

Comparing Strings with trailing spaces in SQL statements

This post was updated on .
Hi,

I am trying to fetch records from my cache based on the value of a column
which is a STRING datatype.
My string column has a few trailing spaces as such - "ABC    ".
When I use the below SQL statement -

SELECT * from Person where fNmae = "ABC";

I find no matches. However, when I use the below query -

SELECT * from Person where fName = "ABC    "

I am able to find the row. So my questions are -

1. Whether Ignite does not TRIM strings internally when doing comparisons?
2. Is there a way to configure my cluster to enforce TRIM whenever there are
SQL statements with String comparisons?

Thanks!



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: Comparing Strings in SQL statements


On Thu, Sep 14, 2017 at 7:10 PM, iostream <[hidden email]> wrote:

1. Whether Ignite does not TRIM strings internally when doing comparisons?
 
I don't think Ignite trims strings for comparison. You should use TRIM() function explicitly when inserting or comparing strings.
 
2. Is there a way to configure my cluster to enforce TRIM whenever there are SQL statements with String comparisons?

I do not think you can enforce trimming without actually using the TRIM() function. 

Do you know other databases that provide the behavior you are asking for? If yes, please give us a link to this feature and we will consider adding it to Ignite. 
 
Sent from: http://apache-ignite-users.70518.x6.nabble.com/

iostream iostream
Reply | Threaded
Open this post in threaded view
|

Re: Comparing Strings in SQL statements

I have used Informix DB before. In Informix string comparisons such as -

SELECT * from Person where fName = "ABC";

return rows even if the column value has trailing spaces. The Informix
engine internally trims strings before comparison. It would be great if a
similar feature could be added to Ignite because performing TRIM() in every
create or update scenario will be expensive from an application point of
view.



--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
dsetrakyan dsetrakyan
Reply | Threaded
Open this post in threaded view
|

Re: Comparing Strings in SQL statements



On Thu, Sep 14, 2017 at 10:02 PM, iostream <[hidden email]> wrote:
I have used Informix DB before. In Informix string comparisons such as -

SELECT * from Person where fName = "ABC";

return rows even if the column value has trailing spaces. The Informix
engine internally trims strings before comparison. It would be great if a
similar feature could be added to Ignite because performing TRIM() in every
create or update scenario will be expensive from an application point of
view.

TRIM() on insert or update is a much better approach in my view, because the trimmed string can be properly indexed, and then the comparisons will use the direct index lookup and perform much better. If the comparison has to trim on the fly, then it will likely be a full scan, not a direct index lookup.

However, to consider this feature for Ignite, I would like to read some documentation from other databases that describes this behavior. Would be great if you could provide a link.