Powershell Sort-Object Name with numbers doesn't properly

Question

I am trying to find .sql files in a folder and filtering them based on the last write time. Based on the last write time I got 4 files as output.

TestScript10.sql
TestScript5.sql
TestScript6.sql
TestScript7.sql

Now when my command tries to do sort-object I am seeing Testscript10 at the top instead of TestScript5, how do I fix this issue?

My Code

$File= Get-ChildItem $FileLocation -Filter *.sql | Where-Object {$_.LastWriteTime -gt $datetime} | Sort-Object Name

$File Output

[DBG]: PS SQLSERVER:\>> $File


    Directory: C:\SQLScripts


Mode                LastWriteTime     Length    Name
----                -------------     ------ ----
-a---         5/22/2014  10:20 AM         61 TestScript10.sql
-a---         5/22/2014  10:16 AM         60 TestScript5.sql
-a---         5/22/2014  10:24 AM         66 TestScript6.sql
-a---         5/22/2014  10:10 AM         24 Testscript7.sql

Since the name is a string it is performing a string comparison. Since "1" comes before "5", they are ordered as you see (it never needs to check the "0" character). If you want them to be ordered otherwise, you'll have to retrieve that part of the name (convert it to an int) and sort by that instead. — Robert Westerlund, May 23 '14 at 00:08
You should name your files like this: TestScript01.sql, TestScript02.sql, etc. (Or "001" or whatever, depending on how many there are.) They way they will sort properly by name. — dan-gph, May 23 '14 at 01:09

score 9 · Answer 1 · edited Sep 18 '18 at 21:37

Thanks for all your suggestions above for my question. After streamlining the suggestions, the code below worked well for my situation. This is helping me sort in the natural order like Windows Explorer does.

$ToNatural= { [regex]::Replace($_, '\d+',{$args[0].Value.Padleft(20)})}
$File= Get-ChildItem $FileLocation -Filter *.sql | Where-Object {$_.LastWriteTime -gt $datetime} | Sort-Object $ToNatural

score 8 · Answer 2 · answered May 23 '14 at 00:11

8

Something like this:

| sort-object {[int]($_.basename -replace '\D')}

answered May 23 '14 at 00:11

mjolinor

66,130
7
114
135

Larry Song · Answer 3 · 2020-02-18T23:31:07.053

Hm... finally figure it out.

Windows explorer is using a legacy API in shlwapi.dll called StrCmpLogicalW when sorting strings.

I don't want to use padding zeros, so wrote a script.

https://github.com/LarrysGIT/Powershell-Natural-sort

Find the following powershell script, it using the same API. You may need to check the latest code from the repo as I am not updating here always.

function Sort-Naturally
{
    PARAM(
        [System.Collections.ArrayList]$Array,
        [switch]$Descending
    )

    Add-Type -TypeDefinition @'
using System;
using System.Collections;
using System.Collections.Generic;
using System.Runtime.InteropServices;
namespace NaturalSort {
    public static class NaturalSort
    {
        [DllImport("shlwapi.dll", CharSet = CharSet.Unicode)]
        public static extern int StrCmpLogicalW(string psz1, string psz2);
        public static System.Collections.ArrayList Sort(System.Collections.ArrayList foo)
        {
            foo.Sort(new NaturalStringComparer());
            return foo;
        }
    }
    public class NaturalStringComparer : IComparer
    {
        public int Compare(object x, object y)
        {
            return NaturalSort.StrCmpLogicalW(x.ToString(), y.ToString());
        }
    }
}
'@
    $Array.Sort((New-Object NaturalSort.NaturalStringComparer))
    if($Descending)
    {
        $Array.Reverse()
    }
    return $Array
}

Find the test results below.

PS> # Natural sort
PS> . .\NaturalSort.ps1
PS> Sort-Naturally -Array @('2', '1', '11')
1
2
11
PS> # If regular sort is used
PS> @('2', '1', '11') | Sort-Object
1
11
2

And,

PS> # Not good
PS> $t = (ls .\Scripts*.txt).name
PS> $t | Sort-Object
Scripts1.txt
Scripts10.txt
Scripts2.txt
PS> # Good
PS> Sort-Naturally -Array $t
Scripts1.txt
Scripts2.txt
Scripts10.txt

Best solution as it does not require any knowledge of input patterns. — Bentoy13, Apr 10 '19 at 10:01
This is a true solution. if you try and sort files that have a file name like: 1.1 file.txt 1.5 file.txt 1.10 file.txt Other solutions will not work where this one does. — Jim, Feb 18 '20 at 18:28

score 0 · Answer 4 · answered May 23 '14 at 05:06

I like @mjolinor solution better, as it shows how powerful sorting in PowerShell can be. But just in case you would like to "fix" file names with prefix mentioned in comments:

Get-ChildItem Test*.sql | 
    Rename-Item -NewName { 'TestScript{0:D2}.sql' -f [int]($_.BaseName -replace '\D') }

Once renamed, files will be sorted as you expected. If you need more than two digits - just change formating ({0:D#}).

Powershell Sort-Object Name with numbers doesn't properly

4 Answers4

Linked