• 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