Replace multiple values on a string with data from another table

[Molkas]

--I have two Sql Server 2012 tables with data similar to these:

-- Table 1
Declare @Product_Sales Table (Suggested_Products varchar(max) null);
Insert Into @Product_Sales
Values
('({1421,1} OR {1422,2}) AND NOT ({1426,2},({1698,0} OR {1699,1} OR {1700,0})'),
('{1698,0} OR {1699,1} OR {1700,0}')

-- Table 2
Declare @Products Table (ProductID varchar(7) not null,Primary Key (ProductID), ProductName varchar(40) null);
Insert Into @Products
Values
('1421','Tangerine_s'),('1422','Tangerine_l'),('1426','Peruvian_Fig'),
('1698','Manila_Mango'),('1699','Passion_Fruit'),('1700','Concorde_Grape')

I cannot make updates to the tables as they get the data from an application. I need query returning results this way:

Products_Suggested (new column name)
1 (Tangerine_s OR Tangerine_l) AND NOT (Peruvian_Fig OR Manila_Mango OR Passion_Fruit OR Concorde_Grape)
2 Manila_Mango OR Passion_Fruit OR Concorde_Grape

Thank you very much in advance,

Rafael

 

[TamarGranor]

Can you create an additional table to support this? Seems to me you could make it easier if you could build a table that lets you list the components for each condition individually and gets rid of what appears to be the additional info you don't need.

First, you'd need to add a primary key to each of these two tables (or create additional tables that have the same item plus primary keys). Then, I'd think a table something like this:

CREATE TABLE ItemPart (iPK INT Primary KEY, iPSTID INT, iPosition INT, iProductID INT)
INSERT INTO ItemPart (iPSTID, iPTID) VALUES (1, '1421', 1), (1, '1422', 2), (1, '1426', 3), (1, '1698', 4), (1, '1699', 5), (1, '1700', 6), ...

You'd have to write code to parse the original data and create that, but then I'd think you could use it to build dynamic SQL and execute it.

Tamar

 

[Molkas]

Hello Tamar. Thank you kindly for replying to my post this quick!
Unfortunately I cannot create the additional table you suggested. The data comes from an application I need to produce reports from. Additionally, the table is very large for trying to find a solution with case statements
What I have tried before was to eliminate the brackets, the commas and the number-like characters after the commas with the Replace function but then I got stuck trying to replace the values of the numeric-like characters with the English translation while retaining the logical operators (OR, AND NOT) and the parentheses.

 

[fredericofonseca]

few questions

1 - what are the possible operators ?
you examples have "OR" and "AND NOT" but are there others
I would also expect "AND" on its own as a operator
2 - can you create a CLR on the server, either on the database where you have this, or on another database specifically created to hold CLR code

3 - if CLR is not possible, can you create user defined functions on same?

4 - what is the max size of the content of the string - you have it defined as varchar(max) but surely there is a limit to it

5 - your example

('({1421,1} OR {1422,2}) AND NOT ({1426,2},({1698,0} OR {1699,1} OR {1700,0})'),
how do you know that "{1426,2},({1698,0}" translates to a "{1426,2} OR ({1698,0}"? and if it is a rule is it always the same rule?

when your are processing this what is the expected volume or rows? 100, 1K, 10K, 1M ?

And finally tell whoever did this design that it is quite a bad one - kill him/her if you can while communicating this.


Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[fredericofonseca]

sample code - assumes that max size of string is 8000 - more will require a CLR if performance is an issue.
It uses a splitter from Jeff Moden - see

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Following code requires that there is a unique id on table @product_sales.
Not on the supplied code sample but it is required
if not available then a further bit of code will be required to assign a unique id to each row processed


Code assumes that the input string blocks will always be as per example - e.g. start with { and end with }
It does not handle what may have been a error on the sample e.g. ,( being converted to a OR
In any case that would be easily deal within the case statement below

declare @Products table
(ProductID varchar(7) not null
, primary key (ProductID)
, ProductName varchar(40) null
);
insert into @Products
    values ('1421', 'Tangerine_s'
    ), ('1422', 'Tangerine_l'
    ), ('1426', 'Peruvian_Fig'
    ),
    ('1698', 'Manila_Mango'
    ), ('1699', 'Passion_Fruit'
    ), ('1700', 'Concorde_Grape'
    )

declare @Product_Sales table
(id int
, Suggested_Products varchar(max) null
);
insert into @Product_Sales
    values (1, '({1421,1} OR {1422,2}) AND NOT ({1426,2},({1698,0} OR {1699,1} OR {1700,0})'
    ),
    (2, '{1698,0} OR {1699,1} OR {1700,0}'
    )

-- sample code to split the strings and join to the products table
-- only to ilustrate one of the steps used
select ps.id
      , ps.Suggested_Products
      , replace(replace(ps.Suggested_Products, '}', '^s}'), '{', '}') as str1 -- not required on final sql
      , dsk.ItemNumber -- not required on final sql
      , dsk.Item
      , p.ProductID -- not required on final sql
      , p.ProductName 
from @Product_Sales ps
outer apply dbo.DelimitedSplit8K(replace(replace(ps.Suggested_Products, '}', '^s}'), '{', '}'), '}') dsk
outer apply (select *
    from @Products p
    where right(dsk.Item, 2) = '^s'
        and p.ProductID = left(dsk.Item, charindex(',', dsk.Item) - 1)
) p

-- build the main sql
-- use the code above as a anchor for XML PATH
select ps.id
      , stuff( -- not need here but left as an example 
       (select ' ' +
               case
               when right(ps1.Item, 2) = '^s'
                   then coalesce(ps1.ProductName, '')
               else ps1.Item
               end
           from (select top 1000 -- max of 1000 splits - may need to be increased
                         ps.id
                       , ps.Suggested_Products
                       --, replace(replace(ps.Suggested_Products, '}', '^s}'), '{', '}') as str1
                       --, dsk.ItemNumber
                       , dsk.Item
                       --, p.ProductID
                       , p.ProductName
                 from @Product_Sales ps
                 outer apply dbo.DelimitedSplit8K(replace(replace(ps.Suggested_Products, '}', '^s}'), '{', '}'), '}') dsk
                 outer apply (select *
                              from @Products p
                              where right(dsk.Item, 2) = '^s'
                              and p.ProductID = left(dsk.Item, charindex(',', dsk.Item) - 1)
                             ) p
                 order by dsk.ItemNumber
                ) as ps1
           where ps1.id = ps.id
           for xml path (''), type
       ).value('text()[1]', 'varchar(max)')
       , 1, 1, '')
       as Emails
from @Product_Sales as ps
group by ps.id

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886