Extract and evaluate range from a give range string

Question

I have a table in which products are stored. Below is the schema with data.

What I want to achieve is that I will pass ProductId, Tenure and Range as parameters and it will pick matched record.

So for example, If I have my Parameters ProductId = 21, Tenure=Duration=1 , and Range =14678 , then it should give me Id=4 as my expected output. Because the range is given 10001-15000.

Answer 1

You can use parsename() in concert with a try_convert()

Example

Declare @YourTable Table ([ID] varchar(50),[ProductID] int,[Price_Range] varchar(50),[Duration] int)
Insert Into @YourTable Values 
 (1,21,'0 - 5000',1)
,(2,21,'5001 - 10000',1)
,(4,21,'10001 - 15000',1)
,(5,21,'15001 - 20000',1)
 
Select * 
 From @YourTable
 Where ProductID = 21 
   and Duration  = 1
   and 14678 between try_convert(int,parsename(replace(Price_Range,'-','.'),2))
                 and try_convert(int,parsename(replace(Price_Range,'-','.'),1))
   

   

Returns

ID  ProductID   Price_Range     Duration
4   21          10001 - 15000   1
  

Note: The try_convert() may not be necessary. Not knowing the full scope of your data, it best to avoid false positives.

Answer 2

I think using parsename() for such purposes is icky -- clever, but icky. SQL Server really should provide the appropriate functions.

So, I would use:

14678 >= try_convert(int, left(price_range, charindex(' ', price_range))) and
14678 <= try_convert(int, stuff(price_range, 1, charindex('-', price_range) + 1, ''))