0

On our webshop, customers can view and order products based on their customer group code. Consider this example:

DECLARE @MyGroup AS VARCHAR(10) = 'GROUP10'
DECLARE @MyGroupFilter AS VARCHAR(10) = '%GROUP10%'

DECLARE @ItemTable TABLE(
    ID VARCHAR(10),
    GroupFilter VARCHAR(100)
)

INSERT INTO @ItemTable SELECT 'ITEM1','GROUP10,GROUP12' -- Only GROUP10 and 12 can view and order this item
INSERT INTO @ItemTable SELECT 'ITEM2','GROUP11,GROUP13' -- Only GROUP11 and 13 can view and order this item


SELECT * 
FROM @ItemTable
WHERE GroupFilter LIKE @MyGroupFilter

In this example the customer has group code GROUP10 and can only order ITEM1, as this has a filter that matches the customers group. So far so good.

Now I have been asked wether it is possible to use placeholders in the product table like:

INSERT INTO @ItemTable SELECT 'ITEM3','GROUP1%,GROUP2%'         -- All GROUP1x and GROUP2x can view and order this item

This item can be ordered by any customer that has a group starting with GROUP1 or GROUP2

I have been 'playing' with this for a while, but can't find a solution so far. Can anyone point me in the right direction?

Thanks!

Roeland
  • 820
  • 1
  • 9
  • 33
  • 2
    You should read ["Is storing a delimited list in a database column really that bad?"](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) and normalize your schema. That would make things easier. – sticky bit Jul 15 '20 at 10:22
  • 2
    Sounds like you have yourself a 1:M relationship between items and groups. You need to update your database model :) – gvee Jul 15 '20 at 10:22

0 Answers0