Monday, August 08, 2011

How do I sort a VARCHAR column in SQL server?

Posted by Rahul Kharde at 3:05 AM

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.



If you enjoyed this post and wish to be informed whenever a new post is published, then make sure you subscribe to my regular Email Updates. Subscribe Now!


Kindly Bookmark and Share it:

YOUR ADSENSE CODE GOES HERE

0 comments:

Have any question? Feel Free To Post Below:

 

Popular Posts

Recent Comments

© 2011. All Rights Reserved | Help to understand .Net | Template by Blogger Widgets

Home | About | Top