Extract integer from a string column

Question

How to extract the just the year values as integer from a column in kusto

.create table inputtable (Status:string)

.ingest inline into table ["inputtable"] <|
"Yr2022_Florida_US"
Yr2015_SA_US
Yr2011_Sydney_AUS
Yr2007_Perth_AUS
Yr2023_Milano_Italy

///////////Below is output table///////////////

.create table outputtable (Year:int)

.ingest inline into table ["outputtable"] <|
2022
2015
2011
2007
2023

Answer 1

Hello Bexy,

there are two solutions to extract integers from a string:

.create table inputtable (Status:string)

.ingest inline into table ["inputtable"] <|
"Yr2022_Florida_US"
Yr2015_SA_US
Yr2011_Sydney_AUS
Yr2007_Perth_AUS
Yr2023_Milano_Italy

inputtable
| project year = toint(substring(Status,2 ,4))

inputtable
| project  year = toint(extract("([0-9.]+)", 1, Status))

The first 'substring' reads a part of the string, starting at the second location 'zero-based' (so, starting with the third character), four characters long.

The second 'extract' solution uses RegEx to parse the string and tries to find groups of numbers. The first group is selected.

---

If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. All community members with similar issues will benefit by doing so. Your contribution is highly appreciated.