This is a fundamentally flawed design, including operators in a column, let alone combining operators and values in a single column is seriously wrong. First suggestion is to amend the schema to a proper relational design.
😎
Regardless of the design, I could not resist putting together a quick query that I think will work
USE tempdb;
GO
DECLARE @SQL_STR NVARCHAR(MAX) = N'';
;WITH Price_Table(Item, Country, City, [Number of Days], Cost )
AS
( SELECT Item, Country, City, [Number of Days], Cost FROM
(VALUES
('Paper' ,'US' ,'New York','<10' ,100)
,('Paper' ,'UK' ,NULL ,'<5' ,150)
,('Paper' ,NULL ,'Chicago' ,'>10' ,200)
,('Pen' ,'China' ,NULL ,'<10' ,250)
)AS X(Item, Country, City, [Number of Days], Cost )
)
,Item_Table(Item, Country, City, [Number of Days])
AS
( SELECT Item, Country, City, [Number of Days] FROM
(VALUES
('Paper' ,'US' ,'New York' , 5)
,('Paper' ,'UK' ,'London' , 3)
,('Paper' ,'US' ,'Chicago' ,15)
,('Pen' ,'China' ,'Shangai' , 5)
,('Paper' ,'China' ,'Beijing' ,15)
,('Paper' ,'US' ,'Chicago' , 5)
) AS X(Item, Country, City, [Number of Days])
)
SELECT @SQL_STR = N'SELECT Item,Country,City,Cost FROM (VALUES' + STUFF((SELECT
N',('+ CHAR(39) + CAST(ISNULL(PT.Item ,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.Country,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.City ,'') AS NVARCHAR(20)) + NCHAR(39) +
N',' + CHAR(39) + CAST(ISNULL(PT.Cost ,'') AS NVARCHAR(20)) + NCHAR(39) +
',(SELECT CASE WHEN ' + CAST(IT.[Number of Days] AS NVARCHAR(12))
+ N' ' + SUBSTRING(PT.[Number of Days],1,1)
+ N' ' + REPLACE(REPLACE(PT.[Number of Days],'>',''),'<','')
+ N' THEN 1 ELSE 0 END))'
FROM Price_Table PT
INNER JOIN Item_Table IT
ON PT.Item = IT.Item
WHERE ((PT.City = IT.City AND PT.Country IS NULL)
OR (PT.Country = IT.Country AND PT.City = IT.City)
OR (PT.City IS NULL AND PT.Country = IT.Country)
)
FOR XML PATH(''),TYPE).value('.[1]','NVARCHAR(MAX)'),1,1,'') + N') AS X(Item,Country,City,Cost,Valid) WHERE X.Valid = 1;';
EXECUTE sp_executesql @SQL_STR;
Results
Item Country City Cost
----- ------- -------- ----
Paper US New York 100
Paper UK 150
Paper Chicago 200
Pen China 250