In a previous post I showed you how to convert strings and summarize the data, in that same post I mentioned some of my weather data was coming in as strings. In this post I have a similar issue. The humidity field is a string, and it contains %. So I will show you how to remove the % from the field so we can convert it to a double.
Projecting the relative_humidity_s field you can see what I’m talking about.
Using substring we can remove the % sign.
Current_Conditions_CL | extend humidty = substring(relative_humidity_s, 0, 2) | project humidty
When using the substring method, the first field is the field you want to remove characters from, in this case ‘relative_humidty_s’ the second field is telling the method where to start from, in this case we’re starting at the beginning at 0. And finally the last field is telling it where to stop, which is at the second character. If this was a 3, it would still bring in the % and if it was 1 it would bring back just the 9.
This is a good method if you always want to remove the same amount of characters. But what if we want to dynamically remove the % sign or any other character, regardless of its location?
We can do that using Trim(). To use Trim, we use the @ followed by double quotes with any characters we want removed inside the quotes.
Current_Conditions_CL | extend humidty = trim(@"%", relative_humidity_s) | project humidty
putting together the final query we can also convert the string to double at the exact same time that we remove percent symbol.
Current_Conditions_CL | extend todouble(humidity = trim(@"%",relative_humidity_s)) | project humidity
Now our data is a double and we can summarize it, build cases around what to do when its good humidity or too humid.
You can read more about substring here and trim here
2 thoughts on “Log Analytics Substring and Trim”
Comments are closed.