CODEWITHSUNDEEP
powershell
WannabeDev
WannabeDev

Reputation: 35

How to compare values of numbers that are at the end of the string?

I created 100 cloud users with UPNs and names CloutTest1 to CloudTest100. How can I filter out/get those users based on their numbers? e.g. users between 30 and 40

I tried 

get-msoluser -all | ? {$_.userprincipalname.replace('CloudTest','') -lt 50} | select DisplayName | Sort-Object -Descending

results 

DisplayName 

CloudTest25 
CloudTest16 
CloudTest32 
CloudTest44 
CloudTest45 
CloudTest37 
CloudTest1  
CloudTest12 
CloudTest26 
CloudTest4  
CloudTest38 
CloudTest34 
CloudTest11 
CloudTest31 
CloudTest35 
CloudTest19 
CloudTest24 
CloudTest39 
CloudTest49 
CloudTest42 
CloudTest36 
CloudTest10 
CloudTest15 
CloudTest18 
CloudTest47 
CloudTest41 
CloudTest27 
CloudTest20 
CloudTest30 
CloudTest2  
CloudTest46 
CloudTest40 
CloudTest22 
CloudTest48 
CloudTest17 
CloudTest23 
CloudTest13 
CloudTest3  
CloudTest43 
CloudTest28 
CloudTest21 
CloudTest100
CloudTest5  
CloudTest33 
CloudTest14 
CloudTest29

I am not sure why it is not sorting and I have no idea where the '100' came from.

How can I get numbers between 30 and 45? Why are those above not sorting?

Upvotes: 2

Views: 202

Answers (3)

user6811411
user6811411

Reputation:

I suggest to use:

  • a Where-Object with the RegEx based -match operator to get only user names starting with CloudTest and
  • capturing the number in a group () automatically stored in the $Matches[1] variable
  • sorting the result with a script block ToNatural by Roman Kuzmin which sorts by first replacing all numbers to an equal length by padding left with zeroes.
Get-MsolUser -All  | Where-Object {($_.userprincipalname -match '^CloudTest(\d+)') -and 
                                   [int]$Matches[1] -lt 50} | 
    Select DisplayName | 
       Sort-Object {[regex]::Replace($_.DisplayName,'\d+',{$args[0].Value.PadLeft(10,"0")})} -Desc

Sample output:

DisplayName
-----------
CloudTest49
CloudTest48
CloudTest47
CloudTest46
CloudTest45
%<...snip...>%

Upvotes: 1

Theo
Theo

Reputation: 61068

As already commented, you are sorting Alphabetically where you expect a Numeric sort. This is why you have to cast the string that holds a number to integer with [int].

Having said that, a UserPrincipal name has the 'Internet-style' format such as [email protected] (you can read about that here)

In your test situation, I would rather do the Where-Object based on the DisplayName property:

Get-MsolUser -All | Where-Object {[int]($_.DisplayName -replace '^CloudTest', '') -lt 50} | 
    Select-Object DisplayName, 
                  @{Name = 'UserNumber'; Expression = {[int]($_.DisplayName -replace '^CloudTest', '')}} | 
    Sort-Object -Property UserNumber -Descending |
    Select-Object DisplayName

If you really want to compare by UserPrincipalName, something like this could do it:

Get-MsolUser -All | 
    Where-Object {[int]($_.UserPrincipalName.Split("@")[0] -replace '^CloudTest', '') -lt 50} | 
    Select-Object DisplayName, 
                  @{Name = 'UserNumber'; Expression = {[int]($_.UserPrincipalName.Split("@")[0] -replace '^CloudTest', '')}} | 
    Sort-Object -Property UserNumber -Descending |
    Select-Object DisplayName

You could have made things easier for you if you would have used leading zeroes for the numbers in the names like 

CloudTest001
CloudTest002
...
CloudTest100

That way, even an alphanumeric sort will show up fine.

Forgot to say: If you want to get users with a number in a certain range, you could simply replace the -lt 50 with -in 30..40 in the Where-Object clause.

Hope that helps

Upvotes: 0

vonPryz
vonPryz

Reputation: 24071

You are bitten by string-integer comparison. Sorting is surprisingly tricky an operation. It's not obvious if sorting is by dictionary order, by magnitude or natural sort order.

In this case, "100" is a string, not a number. As the first character is "1" smaller than "5", that's all that matters. For integers, "100" obviously is larger than "50".

Upvotes: 0

Related Questions