Search code examples
sqlsql-servert-sqlsql-server-2017

Extract and evaluate range from a give range string

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

enter image description here

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.

Solution

  • 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.