cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 

Coding a case insensitive GROUP BY in uSQL

eugenm
Visitor

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.

8 REPLIES 8

eugenm
Visitor

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

DanielS
DynaMight Leader
DynaMight Leader

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

The true delight is in the finding out rather than in the knowing.

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
DynaMight Leader
DynaMight Leader

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.

The true delight is in the finding out rather than in the knowing.

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
DynaMight Leader
DynaMight Leader

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

The true delight is in the finding out rather than in the knowing.

@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.

When passion meets people magic and innovation happen.

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().

 

Featured Posts