check chars in varchar

Your first need to split your variable that you are checking for into rows, and remove duplicates. For only a few characters you could simply use a table valued constructor:

DECLARE @b varchar(5) = 'DCA';SELECT  DISTINCT Letter = SUBSTRING(@b, n.Number, 1)FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS n (Number)WHERE n.Number <= LEN(@b)

Which gives:

Letter----------DCA   

Now you can compare this to your column, and limit it only to columns where the column contains all the letters (done in the HAVING clause)

DECLARE @b varchar(5) = 'DCA';WITH Letters AS(   SELECT  DISTINCT Letter = SUBSTRING(@b, n.Number, 1)    FROM (VALUES(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) AS n (Number)    WHERE n.Number <= LEN(@b))SELECT  *FROM    (VALUES ('AA'), ('ABCD'), ('ABCDEFG'), ('CAB'), ('NA')) AS t (Col)WHERE   EXISTS        (   SELECT  1            FROM    Letters AS l            WHERE   t.Col LIKE '%' + l.Letter + '%'            HAVING  COUNT(DISTINCT l.Letter) = (SELECT COUNT(*) FROM Letters)        );

If your variable can be longer than 10 characters, then you may need to adopt a slightly different string splitting method. I would still use numbers to do this, but would instead use Itzik Ben-Gan's stacked CTE method:

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n (N)),N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),     N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2)SELECT  ROW_NUMBER() OVER(ORDER BY N)FROM    N3;

This will give you a set of numbers from 1 to 10,000, and you can simply add more CTE's and cross joins as necessary to extend the process. So with a longer string you might have:

DECLARE @b varchar(5) = 'DCAFGHIJKLMNEOPNFEDACCRADFAE';WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) AS n (N)),N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),     N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),Numbers (Number) AS (SELECT TOP (LEN(@b)) ROW_NUMBER() OVER(ORDER BY N) FROM N3),Letters AS (SELECT DISTINCT Letter = SUBSTRING(@b, n.Number, 1) FROM Numbers AS n)SELECT  *FROM    (VALUES ('ABCDDCAFGHIJKLMNEOPNFEDACCRADFAEEFG'), ('CAB'), ('NA')) AS t (Col)WHERE   EXISTS        (   SELECT  1            FROM    Letters AS l            WHERE   t.Col LIKE '%' + l.Letter + '%'            HAVING  COUNT(DISTINCT l.Letter) = (SELECT COUNT(*) FROM Letters)        );

You can try like this:

SELECT * FROM yourTable where colname like '%[A]%'                         AND colname like '%[B]%'                        AND colname like '%[C]%'

or you can try using PATINDEX

SELECT * FROM yourTable WHERE PATINDEX('%[ABC]%',colname) > 1

One more version:

DECLARE @a varchar(5) = 'ABCD'DECLARE @b varchar(5) = 'DCA';WITH cte AS(SELECT ROW_NUMBER() OVER( ORDER BY (SELECT NULL)) rnFROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) a(n)CROSS JOIN (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) b(n)),astring AS(SELECT DISTINCT SUBSTRING(@a, rn, 1) AS l FROM cte WHERE rn <= LEN(@a)),bstring AS(SELECT DISTINCT SUBSTRING(@b, rn, 1) AS l FROM cte WHERE rn <= LEN(@b))SELECT CASE WHEN EXISTS(SELECT * FROM bstring WHERE l NOT IN(SELECT * FROM astring))             THEN 0 ELSE 1        END AS result