Coding a case insensitive GROUP BY in uSQL

[eugenm]

Hi,

I know that case insensitivity is not supported in uSQL, at least not outright.

Is there any workaround available whereby I can group by a column (e.g. a "First and Last Name" text column), while supporting any format for the respective field, in any case type? 

End result is that John Doe, john doe, and JOHN DOE are all grouped together, under any of the 3 versions.

If uSQL supported any way to normalize this it would be easy - by changing them all to lower or upper cases, for e.g.

These text entries are not mapped to any userID or other identifier, BTW.

[eugenm]

Bumping this request up... 
Is there really no solution, uSQL/ Dynatrace experts?

[DanielS]

Hi @eugenm string values are case-sensitive. For example, SELECT city FROM usersession WHERE userId LIKE "*dynatrace*" matches me@dynatrace.com but not me@dynaTrace.com. To avoid this, use the ? wildcard character as in this example: SELECT city FROM usersession WHERE userId LIKE "*dyna?race*"

Never tried it personally but maybe you can use some of the above.Maybe some example can be enlightening

[eugenm]

Thanks, but wild cards don't work. 

I have to be able to catch ALL case type variations, as explained in my request - including ALL CAPS.

The wildcard solution may work for capturing lower case and title case, but it's missing all caps.

SELECT COUNT(DISTINCT usersession.stringProperties.techid) AS "TechID", usersession.stringProperties.manager as "Manager" FROM useraction GROUP By usersession.stringProperties.manager 

This should consolidate the number of techid's under each manager, regardless of how manager is spelled, caps wise. The techid counts for manager names listed as "John Doe", "john doe" and "JOHN DOE" muse be grouped together in the output. The normalization can be done under one of the variants, say "JOHN DOE".
The problem is that there is no way to normalize text in uSQL, nothing like UPPER() or LOWER() or any similar function that I know of.

[DanielS]

Understood, I see no option. What I try to do in this case is to change the source, using a normalized List or Choice no allowing the user to type it.

[eugenm]

That's really disappointing. This is a significant product gap, in my opinion.

It is very difficult to address this in the source data, for various reasons.

And this is just one example. There are quite a few data fields that pose this challenge, when it comes to our project needs.

Hopefully, input text field normalization support can be added - via ANY means. And it must support the GROUP BY use case.

Thank you

[DanielS · Accepted solution]

Your welcome, you still can create a Dynatrace Product Idea to request this feature.

[AgataWlodarczyk]

@eugenm Did you submit a product idea based on your needs?
If yes, please share the link to it, so all Community members can be up to date with that case.

[eugenm]

Thanks for reminding me to do it.

I've added it HERE 

I'd be OK with either a Group By with case option, or having support for LOWER() and UPPER().