INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Replace multiple values on a string with data from another table

Replace multiple values on a string with data from another table

(OP)
--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

RE: Replace multiple values on a string with data from another table

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:

CODE

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

RE: Replace multiple values on a string with data from another table

(OP)
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.

RE: Replace multiple values on a string with data from another table

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

RE: Replace multiple values on a string with data from another table

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+Tab...

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

CODE

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
www.syssoft-int.com

FAQ219-2884: How Do I Get Great Answers To my Tek-Tips Questions?
FAQ181-2886: How can I maximize my chances of getting an answer?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close