How to use like clause over string separated with comma inside case statement-CodePudding

DECLARE @IsSearch_  BIT;
SET @IsSearch_ ='True'
DECLARE @Organization_  VARCHAR(100);
SET @Organization_ ='111,111A'

select *
from VYC20 C20
where (@Organization_ IS NULL) 
  OR (1 = CASE @IsSearch_
          WHEN 0 THEN (CASE WHEN @Organization_ IS NOT NULL
                            AND C20.ORG IN 
                                (select value
                                from STRING_SPLIT(@Organization_, ','))
                            THEN 1 ELSE 0 END)
          WHEN 1 THEN (CASE WHEN C20.ORG like @Organization_   '%'
                             THEN 1 ELSE 0 END)
          ELSE 0 END)

In the above query the 1st case WHEN 0 statement with IN clause gives the correct record, but the WHEN 1 THEN gives me 0 records, can someone please help me how to apply like clause with string separated with comma.

CodePudding user response：

It looks like what you actually want is to use LIKE after splitting

DECLARE @IsSearch_  BIT = 1;
DECLARE @Organization_ VARCHAR(100) = '111,111A';

SELECT *
FROM VYC20 C20
WHERE @Organization_ IS NULL
   OR (@IsSearch_ = 0 AND
      C20.ORG IN 
          (SELECT s.value
          FROM STRING_SPLIT(@Organization_, ',') s))
   OR (@IsSearch_ = 1 AND
      EXISTS (SELECT 1
          FROM STRING_SPLIT(@Organization_, ',') s
          WHERE C20.ORG LIKE s.value   '%'));

I strongly advise you to do this in a set-based fashion and use a table variable or Table Parameter

DECLARE @IsSearch_  BIT = 1;
DECLARE @Organization_ TABLE (value VARCHAR(100));
INSERT @Organization_ VALUES('111'),('111A');

SELECT *
FROM VYC20 C20
WHERE @IsSearch_ = -1  -- include all
   OR (@IsSearch_ = 0 AND
      C20.ORG IN 
          (SELECT s.value
          FROM @Organization_ s))
   OR (@IsSearch_ = 1 AND
      EXISTS (SELECT 1
          FROM @Organization_ s
          WHERE C20.ORG LIKE s.value   '%'));