CODEWITHSUNDEEP
sql-servert-sqlsql-like
ParaDoX86
ParaDoX86

Reputation: 39

T-SQL: LIKE operator, compare string to one value

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

Example:

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 ;.

Upvotes: 0

Views: 173

Answers (2)

Aaron Bertrand
Aaron Bertrand

Reputation: 280252

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 (and let's say ColumnA is limited to 128 characters):

CREATE TABLE dbo.BadDesign
(
  ColumnA nvarchar(128),
  ColumnB nvarchar(max)
);
 
INSERT dbo.BadDesign(ColumnA, ColumnB) VALUES
  (N'[a]', N'[a;b;c;d]'), -- only match
  (N'[p]', N'[q;r;s]'),
  (N'[h]', N'[hi;j;k]');

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

Nested Replace

In the old days before (SQL Server 2017), 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:

ColumnA ColumnB
[a] [a;b;c;d]

We can't use TRIM() on versions prior to SQL Server 2017, but I explain below why we don't want to use that function on modern versions anyway.

OpenJson

In 2016+ we can use OPENJSON after a little manipulation to the string. And here I use a PARSENAME() trick which is only safe if the ColumnA <= 128 characters. I show other workarounds in this db<>fiddle:

SELECT b.ColumnA, b.ColumnB 
  FROM dbo.BadDesign AS b
  CROSS APPLY OPENJSON(REPLACE(REPLACE(REPLACE(
    b.ColumnB, N'[', N'["'), N']', N'"]"'), N';', N'","')) AS j
  WHERE j.value = PARSENAME(b.ColumnA, 1);

Results:

ColumnA ColumnB
[a] [a;b;c;d]

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'%';
ColumnA ColumnB
[a] [a;b;c;d]

We don't want to use TRIM() here because we don't simply want to remove the enclosing square brackets; we want delimiters there so we can always compare A to B regardless of where B is in the string. Without surrounding delimiters replaced or translated, we could get inaccurate results if the match is at the beginning or end of the multi-value string.

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
           (
             -- if 2016:
             REPLACE(REPLACE(@s, N'[',N';'),N']', N';'), 
             -- if 2017+, TRANSLATE() is slightly cleaner:
             /* TRANSLATE(@s, 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;
ColumnA ColumnB
[a] [a;b;c;d]

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.

Upvotes: 3

ekochergin
ekochergin

Reputation: 4129

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

Upvotes: 0

Related Questions