Data types and its flaws?

Hey guys,

I have a table with the following information.

A1,A2,A3,A10,A11,A12,A14,A20,A21
B1,B2,B3,B10,B11,B12,B14,B20,B21
C1,C2,C3,C10,C11,C12,C14,C20,C21

Now if I assign this field as a char,varchar,text field then when sorting it turns out
A1,A10,A11,A12,A14,A2,A20 etc. So in order to go around this issue, I placed a blank ' ' (space) before the items that are less than 10 so that it'll sort as
' A1', ' A2', ' A3' , 'A10' etc. However, now the problem is since the B and C series also have this space it actually sorts as
' A1', ' A2', ' A3', ' B1', ' B2' ... 'A11','A12' etc. Now, I know why it is doing that. I'm trying to figure out if there is a solution to this issue. I've considered having two fields instead, one for the letter and another for the number, However, I need to display them together and I also need to do some reporting on these figures, so even if i concatenate the two fields together, it'll be a string field and sort by the same way. Any ideas?

Here is your table and sample data.

CREATE TABLE [dbo].[colSort$](
[col1] [nvarchar](5) )

INSERT [dbo].[colSort$] ([col1]) VALUES (N'A1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'A21')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'B21')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C1')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C2')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C3')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C10')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C11')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C12')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C14')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C20')
INSERT [dbo].[colSort$] ([col1]) VALUES (N'C21')

Solution:

SELECT col1FROM dbo.colSort$

ORDERBYLeft(col1,1),CAST(RIGHT(col1,Len(col1)-1)asINT)