Search code examples
sqlsql-serverdatabasesql-order-byrdbms

How can I correctly order by this varchar field representing a multiple level order using SQL?

I am not so into DB and I am finding the following problem working on a query on a SQL Server DB of an old legacy application. I state that unfortunately I can't change the database structure\fields type (that are pretty ugly)

I have the following situation:

SELECT [Sottocategoria]
    ,[IdSottocategoria]
    ,[IdCategoria]
    ,[Note]
FROM [dbo].[PROT_TITOLARIO]
where IdCategoria = '5'
order by IdSottocategoria

and I am obtaining something like this:

enter image description here

I have highlighted my output error. As you can see I am trying to order by the IdSottocategoria field (that is defined as varchar).

Ordering in this way it is ordering using alphabetical order (and not considering it as a number as I need).

In practice I need an increasing order of this IdSottocategoria field like this:

5.0 --> 5.1 --> 5.2 --> 5.3 --> ............... --> 5.10 --> 5.11 --> ...........

Note that this IdSottocategoria field can contain also valie as: 5.0.0 or 5.0.1 or more "level" as 5.0.1.3.4

How can I try to handle this situation and achive this behavior?

EDIT 1: I am adding the data:

USE [MY_DB]
GO
/****** Object:  Table [dbo].[PROT_TITOLARIO]    Script Date: 8/7/2019 11:38:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PROT_TITOLARIO](
    [IdSottocategoria] [varchar](50) NOT NULL,
    [Sottocategoria] [varchar](500) NOT NULL,
    [IdCategoria] [varchar](50) NOT NULL,
    [Note] [varchar](max) NULL,
 CONSTRAINT [PK_PROT_TITOLARIO_NEW] PRIMARY KEY CLUSTERED 
(
    [IdSottocategoria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0', N'HR', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.0', N'CV', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1', N'test', N'0', NULL)
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0', N'ghjghjg', N'0.1', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0', N'ghghg', N'0.1.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.0', N'sdf', N'0.1.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.0.0', N'fsdfsdfsd', N'0.1.0.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.0.0.0', N'dgdgdfg', N'0.1.0.0.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.0.0.1', N'uiouoi', N'0.1.0.0.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.0.0.2', N'yrtyrtyrtyrt', N'0.1.0.0.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.1.0.0.1', N'HR - fghfhfghf', N'0.1.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.2', N'bla', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.3', N'AAA', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.4', N'dasdas', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.5', N'fghfghfgh', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.6', N'HR - TEST PRIMO LIVELLO', N'0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.6.0', N'HR - TEST SECONDO LIVELLO', N'0.6', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'0.6.0.0', N'HR - TEST TERZO LIVELLO', N'0.6.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1', N'Commerciale', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1.1', N'XXX', N'1', NULL)
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1.1.0', N'Commerciale - bla', N'1.1', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1.2', N'YYY', N'1', NULL)
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1.3', N'ZZZ', N'1', NULL)
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'1.4', N'Commerciale - dsasdasd', N'1', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'2', N'Titolario - TEST LIVELLO 0', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'2.0', N'bhjbhjbhbjb', N'2', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'2.0.0', N'pippo', N'2.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'2.0.1', N'Titolario - TEST LIVELLO 0 - dfgdfgdfg', N'2.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'3', N'Titolario - TEST 2 LIVELLO 0', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'4', N'TEST 3 LIVELLO 0', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'4.0', N'TEST 3 LIVELLO 0 - LIVELLO 1', N'4', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'4.0.0', N'TEST 3 LIVELLO 0 - LIVELLO 2', N'4.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'4.0.0.0', N'TEST 3 LIVELLO 0 - LIVELLO 3', N'4.0.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'4.0.1', N'TEST 3 LIVELLO 0 - DASDASDAS', N'4.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5', N'ORDINAMENTO', N'IWG', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.0', N'ORDINAMENTO - ORD 0', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.0.0', N'ORDINAMENTO - test', N'5.0', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.1', N'ORDINAMENTO - ORD 1', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.10', N'ORDINAMENTO - ORD 10', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.11', N'ORDINAMENTO - ORD 11', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.12', N'ORDINAMENTO - ORD 12', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.2', N'ORDINAMENTO - ORD 2', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.3', N'ORDINAMENTO - ORD 3', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.4', N'ORDINAMENTO - ORD 4', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.5', N'ORDINAMENTO - ORD 5', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.6', N'ORDINAMENTO - ORD 6', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.7', N'ORDINAMENTO - ORD 7', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.8', N'ORDINAMENTO - ORD 8', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'5.9', N'ORDINAMENTO - ORD 9', N'5', N'')
GO
INSERT [dbo].[PROT_TITOLARIO] ([IdSottocategoria], [Sottocategoria], [IdCategoria], [Note]) VALUES (N'IWG', N'Titolario', N'-', N'Categoria')
GO

*

Solution

  • Seems like this does the job:

    SELECT *
FROM [dbo].[PROT_TITOLARIO]
ORDER BY TRY_CONVERT(hierarchyid,'/'+REPLACE(IdSottocategoria,'.','/')+'/');

    Note this puts the "ID" 'IWG' to the top, as it cannot be converted to a hierarchyid. if you want 'IWG' at the bottom, you could do something like this:

    SELECT *
FROM [dbo].[PROT_TITOLARIO] PT
     CROSS APPLY (VALUES(TRY_CONVERT(hierarchyid,'/'+REPLACE(PT.IdSottocategoria,'.','/')+'/')))V(Hid)
ORDER BY CASE WHEN V.Hid IS NULL THEN 1 ELSE 0 END,
         V.Hid,
         PT.IdSottocategoria;

    DB<>Fiddle