In this programming tutorial we will learn how to sort the varchar Column in ms sql server. We always face the sorting problem of varchar Column.
I have a column Desc of varchar(255 size) datatype in users table, I wanted to get the Desc in sorted order. There is one more column which is auto generated int type.
Below Example is helping you to how to sort out the varchar column.
CREATE TABLE [dbo].[users](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Desc] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_Testing] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
| ID | Desc |
| 1 | A1 |
| 2 | 1 |
| 3 | 10 |
| 4 | B10 |
| 5 | 12 |
ORDER BY converts everything to an int (with a huge value for non-numeric, to sort last) then the last part takes care of alphabetic.
select id,Desc
from users
order by
case IsNumeric(Desc)
when 1 then Replicate(Char(0), 100 - Len(Desc)) + Desc
else Desc
end
Out Put:
| ID | Desc |
| 2 | 1 |
| 3 | 10 |
| 5 | 12 |
| 1 | A1 |
| 4 | B10 |
Performance won't be too great with all that casting going on, so another approach is to add another column to the table in which you store an integer copy of the data and then sort by that first and then the column in question. This will obviously require some changes to the logic that inserts or updates data in the table, to populate both columns. Either that, or put a trigger on the table to populate the second column whenever data is inserted or updated.
Comments
Post a Comment