Sql query based on Substring,Charindex functions

Hi Guys I have one question on a sql query if you know any one, if they can help to write a query. My question is if there is a string example1 "450 lbs weight capacity" example 2 "10-40 weight capacity" the result should be displayed for the first example minimum value maximum value weight 0 450 lbs Second example minimum value maximum value weight 10 40 lbs The results should be displayed in this way if you know some one please help me . --------------------------------------------------------------------------------------------
Solution
create table #temp(test varchar(2000)) insert into #temp values('450 lbs weight capacity')
insert into #temp values('10-40 weight capacity')
insert into #temp values('560-600 lbs weight capacity')
insert into #temp values('')
insert into #temp values(null)
insert into #temp values('ab')
--select * from #temp
select *,replace(substring(test,0,charindex('weight',test)),'lbs','') as value ,CASE WHEN CHARINDEX('-',replace(substring(test,0,charindex('weight',test)),'lbs',''))=0 THEN 0 ELSE SUBSTRING(replace(substring(test,0,charindex('weight',test)),'lbs',''),0,CHARINDEX('-',replace(substring(test,0,charindex('weight',test)), 'lbs',''))) END as minimum ,CASE WHEN CHARINDEX('-',replace(substring(test,0,charindex('weight',test)),'lbs',''))=0 THEN replace(substring(test,0,charindex('weight',test)), 'lbs','') ELSE SUBSTRING(replace(substring(test,0,charindex('weight',test)),'lbs',''),CHARINDEX('-',replace(substring(test,0,charindex('weight',test)), 'lbs',''))+1,LEN(replace(substring(test,0,charindex('weight',test)),'lbs',''))) END as maximum, 'lbs' as weightq from #temp