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.
Shady Hanna wrote:
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
Carlos López Santibáñez Jácome wrote:
Thanks but this won't help. The comparison is by default case insensitive. I wanted it to be case sensitive.
Kilian Hekhuis wrote:
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.
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?
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?
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
If you need case sensitive string compare in an expression the following should work:
if (Index(Var1, Var2) = 0 and Length(Var1) = Length(Var2), ...
Hi James,
Please, check both the date of the last post and the actual question, before you answer. Also, that's a really strange way to do case insensitive comparison...
The normal text-compare you mention is case insensitive, right? I showed one way to do a case-sensitive string comparison in an OutSystems expression without using the BinaryData API as shown earlier in this thread. If there's a better way though let us know.
First, I meant "case sensitive", sorry for the mix-up. Outside queries, string comparison is case sensitive, so no need to use Index and Length. To use case sensitive compare in a database, you need an SQL and a COLLATE (at least in MSSQL), like was mentioned above.
- col_name COLLATE utf8mb4_0900_as_cs LIKE 'a%'
- col_name LIKE 'a%' COLLATE utf8mb4_0900_as_cs
- col_name COLLATE utf8mb4_bin LIKE 'a%'
- col_name LIKE 'a%' COLLATE utf8mb4_bin
can solve this issue like any one of this example and if u need to read more on this quarry statement from here--> mysql