How can we split a string and apply between condition or less than greater condition in stored proce-CodePudding

I have a stored procedure and there are multiple filters there is a filter of salary range I am getting input min and max as string separated by comma like 200,5000 it's working fine, but now I need to include multiple ranges with separated like 2,400|400,800 and I need to split them and add condition between or less than greater than - please help.

IF (@Salary <> '')      
BEGIN
    SELECT @salaryStr = '>= '   
                        REPLACE(@Salary, ',' , ' AND Salary <= ')   '';
    SET @query = @query   ' AND Salary  '   @salaryStr   ' '
END

It's working fine for one range, but I need something to work with multiple ranges as mentioned above. I am sending ranges as string 2,400|400,700 - how can I achieve this? I tried to use string_split function of SQL Server, but could not get it to do what I need - not sure how to use that in this scenario.

CodePudding user response：

Take '2,400|400,800','|' as an example:

select a.value      as range,
       min(b.value) as from_value,
       max(b.value) as to_value
  from string_split('2,400|400,800','|') a
  cross apply string_split(a.value,',') b
 group by a.value;

Result:

range  |from_value|to_value|
------- ---------- -------- 
2,400  |2         |400     |
400,800|400       |800     |

You may want to CAST from/to_value(s) to proper data types

EDIT: to apply the values to range condition, in this example, the multiple ranges are continuous. Therefor, pick the min() from_value and the max() to_value for the dynamic SQL:

select min(b.value) as from_value,
       max(b.value) as to_value
  from string_split('2,400|400,800','|') a
  cross apply string_split(a.value,',') b;

from_value|to_value|
---------- -------- 
2         |800     |