A simple note, and couldn’t find straight answer when i googled!!. so here it is..
This is to get result from a table when filtering in string ranges that will have a wild card for ex: ‘*’
declare @sv varchar(17),@ev varchar(17)
declare @sindex int, @eindex int
set @sv = ‘ABCDE11A09′
set @ev = ‘ABCDE11A79′
– Check the * pos if exists,if doesn’t exists return the len +1 , assuming the * at the last
select @sindex = case when charindex(‘*’,@sv)> 0 then charindex(‘*’,@sv) else len(@sv)+1 end
select @eindex = case when charindex(‘*’,@ev)> 0 then charindex(‘*’,@ev) else len(@ev)+1 end
– remove the * from the range strings
select @sv = case when charindex(‘*’,@sv)> 0 then substring(@sv,0,@sindex) else @sv end
select @ev = case when charindex(‘*’,@ev)> 0 then substring(@ev,0,@eindex) else @ev end
select @sindex,@eindex,@sv,@ev
select mycolumn,substring(VIN,0,@sindex), substring(VIN,0,@eindex) from mytable
where
substring(mycolumn,0,@sindex) >= @sv AND
substring(mycolumn,0,@eindex) <= @ev
ORDER BY mycolumn
