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

Question

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?

A UPN is an Internet-style name, such as `CloudTest21@YourDomain.com`. You are showing a list of DisplayNames. If you replace `CloudTest` in the UPN, you are keft with `21@YourDomain.com` which does not compare to the integer value of 50.. — Theo, Jan 29 '19 at 09:33
`$_.userprincipalname.replace('CloudTest','')` -> `[int]$_.userprincipalname.replace('CloudTest','')` — Ansgar Wiechers, Jan 29 '19 at 09:34
As for sorting: The sort you are trying is an alphabetical sort. What you want is a Numeric sort. You could have done better using your testusers `CloudTest001` to `CloudTest100` — Theo, Jan 29 '19 at 09:35
@Theo Not necessarily. Numeric sorting could be done like this: `Sort-Object {[int]$_.userprincipalname.replace('CloudTest','')}`. — Ansgar Wiechers, Jan 29 '19 at 09:36
@AnsgarWiechers Yes, I know, but the OP could have made his/her life a lot easier by using leading zeroes in the names of the testusers. That's all I'm saying here. Also, see my first comment about UPN format. The `replace('CloudTest','')` would not be enough as you are still left with the remainder of the string. — Theo, Jan 29 '19 at 09:40
True, but we don't know if modifying the UPNs is an option for the OP. — Ansgar Wiechers, Jan 29 '19 at 09:47
Hello, ````[int]$_.userprincipalname.replace('CloudTest','')```` results in ````Cannot convert the "System.Object[]" value of type "System.Object[]" to type "System.Int32".```` Changin UPNs is surely an option. What would be the best way to create users with 001 010 100 etc at the end? — WannabeDev, Jan 29 '19 at 22:20
If your user object has multiple UPNs you need to either pick one to cast to an int, or cast all of them to an int array (`[int[]]`). — Ansgar Wiechers, Feb 04 '19 at 00:43

score 1 · Answer 1 · answered Jan 29 '19 at 13:40

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

score 0 · Answer 2 · answered Jan 29 '19 at 09:40

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

Theo · Accepted Answer · 2019-01-29T10:12:15.940

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 CloudTest21@YourDomain.com (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

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

3 Answers3