Is there a way to compare multiple values in one column to a single value in another column.

Example:

• Column A contains: [a;b;c;d]
• Column B contains: [a]

At the moment I'm using the LIKE operator to achieve this but not result. I tried it with a wildcard % but I get no match because of the ;.

CodePudding user response：

As Larnu suggested, the real fix here is to fix the design. You should go back to the owners and remind them that the database is for storing relational data; if you're jamming multiple "facts" into a single column, you may as well be using a flat file. The exception is if you are storing a comma-separated list for the application and only the application is responsible for assembling and exploding that set.

Anyway, given that you are probably stuck with this:

CREATE TABLE dbo.BadDesign
(
  ColumnA nvarchar(255),
  ColumnB nvarchar(max)
);
 
INSERT dbo.BadDesign(ColumnA, ColumnB) VALUES
  (N'[a]', N'[a;b;c;d]'),
  (N'[p]', N'[q;r;s]');

You can see the following solutions demonstrated in this db<>fiddle:

Nested Replace

In the old days, we would perform nested REPLACE() calls to get rid of the square brackets and replace each end of the string with delimiters:

-- All versions
 
SELECT ColumnA, ColumnB
FROM dbo.BadDesign 
WHERE REPLACE(REPLACE(ColumnB, N'[',N';'),N']', N';')
      LIKE N'%'   REPLACE(REPLACE(ColumnA ,N'[',N';'),N']', N';')   N'%';

Gross, but results:

Translate

In SQL Server 2017, it can be a little less gross with TRANSLATE():

-- SQL Server 2017 
 
SELECT ColumnA, ColumnB 
FROM dbo.BadDesign
WHERE TRANSLATE(ColumnB, N'[]',N';;')
      LIKE N'%'   TRANSLATE(ColumnA, N'[]',N';;')   N'%';  

Split Function

Alternatively, you could create this function on SQL Server 2016 (or a messier one that doesn't use STRING_SPLIT() in earlier versions - as Smor noted, a search will turn up hundreds of those):

CREATE FUNCTION dbo.SplitAndClean(@s nvarchar(max))
RETURNS TABLE
WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT value 
      FROM STRING_SPLIT
           (
             REPLACE(REPLACE(@s, N'[',N';'),N']', N';'), 
             N';'
           )
      WHERE value > N''
  );

Then you can say:

SELECT bd.ColumnA, bd.ColumnB
  FROM dbo.BadDesign AS bd
  CROSS APPLY dbo.SplitAndClean(bd.ColumnA) AS a
  CROSS APPLY dbo.SplitAndClean(bd.ColumnB) AS b
  WHERE a.value = b.value;

But in the end...

...these are all gross "solutions" masking bad design, and you should really have them reconsider how they're using the database.

I know that many shops can't just switch to passing sets between the app and the database using TVPs, because several client providers and ORMs haven't quite had more than a decade to catch that train. If you can't use TVPs or can't change the app, you should at least consider intercepting the comma-separated list passed by the app and break it apart using SPLIT_STRING() or the like. Then you can store the values relationally and let the database do what the database was designed to do, without being handcuffed by app limitations.

CodePudding user response：

If there will be always only one value in col_b like in your example, you can user nested replace function to remove [ and ] and then use "like" for search

select *
  from test_data
 where col_a like '%'   replace(replace(col_b, '[', ''), ']', '')   '%';

But

if there could be more than value in col_b and it could be in any order (e.g. "[a;c]" or "[d;a]") you'll find answer among already answered questions or you may google for "string_split()" function on msdn. The latter has great examples section that will definitely help you out