order by string same integer

[mofareh 2]

hello frendes ,

can i sort data same the attachment  photo ? by order by in sql server

 

[programmerdelphi2k 235]

if using "strings" (in your case), I think that you need fill left-side with "0..." when save your data, or replacing when necessary on code!

Quote

1.2.09...  <---

1.2.10...

// you can "before store it on table... using Before Insert/Post event or a stored-procedure", apply some mask?
procedure TForm1.Button1Click(Sender: TObject);
var
  LArr: TArray<string>;
begin
  LArr := ['1.2.10', '1.1.10', '1.2.9', '1.2.10', '1.1.9', '1.2.1'];
  TArray.Sort<string>(LArr);
  Memo1.Lines.AddStrings(LArr);
  //
  // normalized
  Memo1.Lines.Add('---- Normalized ----');
  LArr := ['1.2.10', '1.1.10', '1.2.09', '1.2.10', '1.1.09', '1.2.01'];
  TArray.Sort<string>(LArr);
  Memo1.Lines.AddStrings(LArr);
end;

Edited January 14, 2023 by programmerdelphi2k

[mofareh 2]

tank you for reply, but there is anather data has only one dot same :

1.1

1.2

i want to sort all data 

[programmerdelphi2k 235]

ok, but see as used on index:

• space =char(32)
• "0" = char(48)
• "1" = char(49)
• "9" =char(57)
• "." = char(58)

[mofareh 2]

i have error in 

TArray.Sort<string>(LArr);

Edited January 14, 2023 by mofareh

[programmerdelphi2k 235]

You need add "Uses clause" before;  // What's your RAD Studio?

Quote

uses
  System.Generics.Collections;
 

 

Edited January 14, 2023 by programmerdelphi2k

[programmerdelphi2k 235]

for reading:

https://www.sqlshack.com/top-five-considerations-for-sql-server-index-design/

[mofareh 2]

15 minutes ago, programmerdelphi2k said:

You need add "Uses clause" before;  // What's your RAD Studio?

 

try it by this data it is not done , the data is ready no insert or update data 

procedure TForm2.Button1Click(Sender: TObject);
var
  LArr: TArray<string>;
begin

  LArr := ['1','1.1','1.1.1','1.1.1.1','1.1.1.2','1.1.1.3','1.1.1.4','1.1.1.5','1.1.1.6','1.1.1.7','1.1.2'
            ,'1.1.2.1','1.1.2.2','1.1.3','1.1.3.1','1.1.4','1.1.4.1','1.1.4.2','1.1.5','1.1.5.1','1.1.6','1.1.6.1'
            ,'1.1.6.2 ','1.2','1.2.1','1.2.1.1','1.2.1.10','1.2.1.11','1.2.1.2','1.2.1.3','1.2.1.4','1.2.1.5'
            ,'1.2.1.6','1.2.1.7','1.2.1.8','1.2.1.9' ];
  TArray.Sort<string>(LArr);
  Memo1.Lines.AddStrings(LArr);
  //
  // normalized
  Memo1.Lines.Add('---- Normalized ----');
  LArr := ['1','1.1','1.1.1','1.1.1.1','1.1.1.2','1.1.1.3','1.1.1.4','1.1.1.5','1.1.1.6','1.1.1.7','1.1.2'
            ,'1.1.2.1','1.1.2.2','1.1.3','1.1.3.1','1.1.4','1.1.4.1','1.1.4.2','1.1.5','1.1.5.1','1.1.6','1.1.6.1'
            ,'1.1.6.2 ','1.2','1.2.1','1.2.1.1','1.2.1.10','1.2.1.11','1.2.1.2','1.2.1.3','1.2.1.4','1.2.1.5'
            ,'1.2.1.6','1.2.1.7','1.2.1.8','1.2.1.9'];
  TArray.Sort<string>(LArr);
  Memo1.Lines.AddStrings(LArr);

end;

[Stefan Glienke 1905]

The default string comparison is done via lexicographic order - what you need is natural sorting - you can achieve this by providing a custom comparer to TArray.Sort that handles that - on windows you can use https://learn.microsoft.com/en-us/windows/win32/api/shlwapi/nf-shlwapi-strcmplogicalw for that

Edited January 14, 2023 by Stefan Glienke

[programmerdelphi2k 235]

hi @mofareh

Pay attention:

• your data in your table it's stored like :  1.1, 1.2 ..... 1.2.1, 1.2.2....
  • let's say that you used this mask:    n.n.nnn    ( n = digits )
  • then, you'll need update your table values to:
    • 1.1.001, 1.1.002 .... 1.2.001, 1.2.002 ....
• for this, you'll need just "edit the values already stored", and your index will be uptadted!

NOTE IMPORTANT: if "this values is used in some relationship between tables, then, you'll need another approach, or some maneuvers to change the size of the fields involved, for example!
Now, if you don't use these fields to create relationships between tables, then it will be easier to update:
for example:

• Create a new field in the table with the desired size to accommodate the new values
• now, read each record and get the old value, make the necessary changes to have the new format, and then save this new value in the new field;
• if all goes well, then now you can decide whether or not you want to delete the old column (field) from your table, and use the new field by default!

[Attila Kovacs 617]

you should order by len(field), field

Edited January 14, 2023 by Attila Kovacs

[mofareh 2]

thank you for all

it is done by 

ORDER BY  Cast ('/' + acc_numb + '/' AS  HIERARCHYID)

[programmerdelphi2k 235]

on code you can try this:

procedure TForm1.Button1Click(Sender: TObject);
var
  LArr    : TArray<string>;
  LArrMask: TArray<integer>;
  LText   : string;
  LCounter: integer;
  LZeros  : integer;
begin
  Memo1.Lines.Clear;
  Memo2.Lines.Clear;
  //
  // NOT normalized
  LArr := ['1', '1.1', '1.1.1', '1.1.1.1', '1.1.1.2', '1.1.1.3', '1.1.1.4', '1.1.1.5', '1.1.1.6', '1.1.1.7', '1.1.2', { }
  '1.1.2.1', '1.1.2.2', '1.1.3', '1.1.3.1', '1.1.4', '1.1.4.1', '1.1.4.2', '1.1.5', '1.1.5.1', '1.1.6', '1.1.6.1',    { }
  '1.1.6.2 ', '1.2', '1.2.1', '1.2.1.1', '1.2.1.10', '1.2.1.11', '1.2.1.2', '1.2.1.3', '1.2.1.4', '1.2.1.5',          { }
  '1.2.1.6', '1.2.1.7', '1.2.1.8', '1.2.1.9'];
  //
  TArray.Sort<string>(LArr);
  Memo1.Lines.AddStrings(LArr);
  //
  LArrMask := [1, 4, 2, 3]; // your mask! -> try LArrMask := [0, 2, 0, 3];
  //
  for var i: integer := 0 to high(LArr) do
    begin
      LText    := '';
      LCounter := 0;
      //
      for var A in LArr[i].Trim.Split(['.']) do // Trim or "replace all space-chars"  like in '1.1.6.2 '
        begin
          LZeros := (LArrMask[LCounter] - A.Length); // better without "Abs()"
          //
          if (LZeros > 0) then
            LText := LText + '.' + ''.Create('0', LZeros) + A
          else
            LText := LText + '.' + A;
          //
          LCounter := LCounter + 1;
        end;
      //
      LArr[i] := LText.Remove(0, 1);
    end;
  //
  TArray.Sort<string>(LArr);
  Memo2.Lines.AddStrings(LArr);
end;

      LArrMask := [0, 2, 0, 3]; 

 

NOTE: you can find the "mask values" scanning all items array, and with "Split()" function + Length = deteminated what the values for your mask!  x = max value between 1 and length(text)

Edited January 14, 2023 by programmerdelphi2k

[Lars Fosdal 1732]

19 hours ago, Stefan Glienke said:

The default string comparison is done via lexicographic order - what you need is natural sorting - you can achieve this by providing a custom comparer to TArray.Sort that handles that - on windows you can use https://learn.microsoft.com/en-us/windows/win32/api/shlwapi/nf-shlwapi-strcmplogicalw for that

uses
  WinApi.ShLwApi, ...

type
  TCompareLogical = class(TComparer<String>)
    function Compare(const Left, Right: string): Integer; override;
  end;

function TCompareLogical.Compare(const Left, Right: string): Integer;
begin
  Result := WinApi.ShLwApi.StrCmpLogicalW(@Left[1], @Right[1])
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  LArr: TArray<string>;
begin
  LArr := ['1','1.1','1.1.1','1.2.1', '1.1.1.1','1.1.1.2','1.1.1.3','1.1.1.4','1.1.1.5','1.1.1.6','1.1.1.7','1.1.2'
            ,'1.1.2.1','1.1.2.2','1.1.3','1.1.3.1','1.1.4','1.1.4.1','1.1.4.2','1.1.5','1.1.5.1','1.1.6','1.1.6.1'
            ,'1.1.6.2 ','1.2','1.2.1.1','1.2.1.10','1.2.1.11','1.2.1.2','1.2.1.3','1.2.1.4','1.2.1.5'
            ,'1.2.1.6','1.2.1.7','1.2.1.8','1.2.1.9' ];
  
  var Comp := TCompareLogical.Create;
  TArray.Sort<string>(LArr, Comp);
  Memo1.Lines.AddStrings(LArr);

Thanks, @Stefan Glienke, I didn't know about that function.

 

I also did not know about HIERARCHYID in SQL, so thank you for that one, @mofareh

ORDER BY  Cast ('/' + acc_numb + '/' AS  HIERARCHYID)