SQL with Manoj

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