String Comparison

String Comparison

  

Dears,

I need to filter the result in an aggregate based on a string variable.

However, it seems the string comparison is by default case insensitive. 

How to make it case sensitive, so that ABC is not equal AbC.

Thanks.

Shady Hanna

Hi Shady,

String comparison is indeed not case sensitive. This is a feature of the underlying database (MS SQL). If you Google on "case insensitive search tsql" or the like (without the quotes), you'll find some answers that may be of use. Note however, that in general you would want case insensitive search, so use it sparingly.

Hi Kilian,

Thanks for your answer.

I fixed it by converting both strings to binary and using the "compare" function of the binary API.


Hi Shady,

I don't quite understand. You were talking about an Aggregate, but I'm not aware of a binary API with regards to Aggregates. So what did you do?

Shady Hanna wrote:

Hi Kilian,

Thanks for your answer.

I fixed it by converting both strings to binary and using the "compare" function of the binary API.


This method works, however I think is a bit too CPU usage intensive, and the conversion might slow down for large amounts of text. I would suggest just using a toUpper() or toLower() on both sides of the text comparison at the filter to get a case insensitive comparison. Something along the lines of 


  toUpper(YourEntitiy.Attribute) = toUpper(YourParam)


Hope this helps!

 -CLSJ

Hi Kilian,

I used something like this in an if statement:

Compare(TextToBinaryData(c), TextToBinaryData(GetUrlMappingsByCode.List.Current.UrlMapping.Code))

Carlos López Santibáñez Jácome wrote:

Shady Hanna wrote:

Hi Kilian,

Thanks for your answer.

I fixed it by converting both strings to binary and using the "compare" function of the binary API.


This method works, however I think is a bit too CPU usage intensive, and the conversion might slow down for large amounts of text. I would suggest just using a toUpper() or toLower() on both sides of the text comparison at the filter to get a case insensitive comparison. Something along the lines of 


  toUpper(YourEntitiy.Attribute) = toUpper(YourParam)


Hope this helps!

 -CLSJ

Thanks but this won't help. The comparison is by default case insensitive. I wanted it to be case sensitive.


You should be able to do it using an advansed sql query, for example


Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS 

This is from the link that Kilian provided. If you use an external extension or action to do the compare such as converting to binary then you will invalidate any possibility of SQL being able to use indexes which could have a major performance impact on larger amounts of data

Shady Hanna wrote:

Hi Kilian,

I used something like this in an if statement:

Compare(TextToBinaryData(c), TextToBinaryData(GetUrlMappingsByCode.List.Current.UrlMapping.Code))

I'm not sure I understand: you can't use this in an Aggregate, but if it's not an Aggregate you're aiming at, a normal Text compare works fine?


Kilian Hekhuis wrote:

Hi Shady,

String comparison is indeed not case sensitive. This is a feature of the underlying database (MS SQL). If you Google on "case insensitive search tsql" or the like (without the quotes), you'll find some answers that may be of use. Note however, that in general you would want case insensitive search, so use it sparingly.

Hi Kilian,

I am struggling with a related subject.

This entity I have contains a text attribute which has case sensitive content. Now when I try to create a unique index on this attribute (since it is the business key) I get an error on account of the fact that I have the two following values in my data: "bifenthrin"and "Bifenthrin".

We are in the Outsystems cloud. Now from what I read Outsystems requires onpremise installations on SQL server to have a CI collation. So I assume (and conclude) that cloud is no different.

The rationale behind this is not documented as far as I can tell and the reason why this is done is really beyond my imagination. Do you have any clue?

Cheers,

Toine.