CREATE TABLE [dbo].[cunsumption_detail](
	[form38_no] [varchar](15) NULL,
	[isused] [bit] NULL,
	[used_Date] [datetime] NULL,
	[Demand_No] [varchar](14) NULL,
	[forConsumptionReport] [bit] NOT NULL
)

Column_name	Type	Computed	Length	Prec	Scale	Nullable	TrimTrailingBlanks	FixedLenNullInSource	Collation
form38_no	varchar	no	15	     	     	yes	no	yes	SQL_Latin1_General_CP1_CI_AS

DECLARE @form_prefix varchar(5), @form_No1 varchar(10), @form_No2 varchar(10)
SET @form_prefix = 'AA' 
SET @form_No1 = 11
SET @form_No2 = 110

Select * FROM cunsumption_detail  
WHERE RTRIM(LTRIM(form38_no)) BETWEEN @form_prefix+'-'+@form_No1 and @form_prefix+'-'+@form_No2 
AND CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT)>=@form_No1
AND CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT)<=@form_No2
ORDER BY SUBSTRING(form38_no,0,CHARINDEX('-',form38_no)) ASC, 
CAST(REVERSE(LEFT(REVERSE(form38_no),CHARINDEX('-',REVERSE(form38_no))-1)) as INT) ASC

form38_no	isused	used_Date	Demand_No	forConsumptionReport
AAM-106	0	1/18/10 3:01 PM	1018999000014	0
AAM-108	0	1/18/10 7:56 PM	1018999000014	0
AA-77	0	1/19/10 1:14 PM	NULL	0
AA-78	0	1/19/10 1:15 PM	NULL	0
AA-79	0	1/19/10 1:23 PM	NULL	0
AA-80	0	1/19/10 1:24 PM	NULL	0
AA-76	0	1/19/10 1:30 PM	NULL	0
AA-33	0	1/20/10 11:16 AM	NULL	0

Select *
FROM cunsumption_detail   
WHERE LEFT(form38_no, CHARINDEX('-',form38_no)-1) = @form_prefix  
AND CONVERT(INT, RIGHT(RTRIM(form38_no), LEN(form38_no) - CHARINDEX('-',form38_no))) BETWEEN @form_No1 AND @form_No2
ORDER BY form38_no