Advertisements
Home > SQL Tips > T-SQL Query for SORTing Numbers stored as String (VARCHAR)

T-SQL Query for SORTing Numbers stored as String (VARCHAR)


Many people come to me as a point of SQL reference and ask, “How can we sort the numbers if they are stored in a VARCHAR column in a table?”.
This morning one guy approached me and asked the same question again.
So this as a generic question I thought to post it in my blog.

Let’s check this by a simple example:

-- Create table and insert some test data:
DECLARE @StringNbrs TABLE (nbr VARCHAR(10))

INSERT INTO @StringNbrs
SELECT C
FROM (VALUES ('681'),
	('21'),
	('9'),
	('1'),
	('401'),
	('158'),
	('1000')) AS T(C)

-- Using simple ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY nbr
Output:-

nbr
1
1000
158
21
401
681
9

The above query with ORDER BY clause won’t sort the nbr column as required.

-- =====================================================================
-- Let's see how can we sort the nbr column with 2 methods given below:-
-- =====================================================================

-- Method #1: Using CAST/CONVERT with ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY CAST(nbr as INT)

-- Method #2: Another approach by using REPLICATE function in ORDER BY clause:
SELECT nbr
FROM @StringNbrs
ORDER BY REPLICATE('0',4-LEN(nbr)) + nbr
Sorted Output:-

nbr
1
9
21
158
401
681
1000
Advertisements
Categories: SQL Tips Tags: ,
  1. naveen
    November 27, 2012 at 3:45 pm

    this very useful, in case i have both numeric and String values in Varchar Column
    1,2,100,200,A1,B1,T1,T100 … and i want this sort as follow 1,2,100,200,A1,B1,T1,T100

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: