CODEWITHSUNDEEP
joinconditional-statementscaseazure-data-explorer
Buddy26
Buddy26

Reputation: 39

How to convert the columns to lowercase in join condition in Kusto Database

TableA
| where GuidId == "123"
| where Desc has_any ("processor")
| join kind=leftouter TableB on 
  $left.SubId == $right.SubId,  
  $left.ProductName == $right.Name,
  $left.GuidId == $right.GuidId
| distinct SubId, PriceTags, ResourceType, ProductName, Name

ProductName is in lower case and Name is in camel case. How to bring ProductName and Name to same case in Join condition.

Thanks

Upvotes: 1

Views: 3172

Answers (2)

Yoni L.
Yoni L.

Reputation: 25955

You'll need to 'normalize' the values before the join.

  • Ideally you'll do this before ingestion, or at ingestion time (using an update policy).
  • Given the current non-normalized values, you can do it at query time (performance would be sub-optimal):
TableA
| where GuidId == "123"
| where Desc has "processor"
| join kind=leftouter (
    TableB
    | extend Name = tolower(Name)
) on 
  $left.SubId == $right.SubId,  
  $left.ProductName == $right.Name,
  $left.GuidId == $right.GuidId
| distinct SubId, PriceTags, ResourceType, ProductName, Name

Upvotes: 2

rony l
rony l

Reputation: 6022

something like:

| extend Name=tolower(Name)

TableA 
| where GuidId == "123" 
| where Desc has_any ("processor") 
| join kind=leftouter (TableB | extend Name=tolower(Name)) on $left.SubId == $right.SubId, $left.ProductName==$right.Name, $left.GuidId==$right.GuidId 
|distinct SubId, PriceTags, ResourceType, ProductName, Name

extend Name=tolower(Name)

Upvotes: 1

Related Questions