We need a query that returns each field that contains the specified letter

select *
from table
where keyword like '%a%'
and keyword like '%b%'

      

        
        
        
      

    

ps It will be very slow. You can look into full text indexing solutions.

All answers given using LIKE

are completely valid, but as noted, they will all be slow. Therefore, if you have a lot of queries and not too many changes in your keyword list, it is worth creating a structure that allows you to execute queries faster.

Here are some ideas:

If you only want the letters az and don't need uppercase and lowercase letters, you can add containsA .. containsZ columns and prepopulate those columns:

UPDATE table
SET containsA = 'X' 
WHERE UPPER(your_field) Like '%A%';

      

        
        
        
      

    

(and so on for all columns).

Then index the Contains .. columns and your query will be

SELECT 
FROM your_table
WHERE containsA = 'X'
AND containsB = 'X'

      

        
        
        
      

    

This can be normalized in an "index table" which can be translated into columns your_table_key

, letter, indexed letter-column, and your query looks like

SELECT
FROM your_table 
WHERE <key> in (select a.key
    From iTable a join iTable b and a.key = b.key
    Where a.letter = 'a'
    AND b.letter = 'b');

      

        
        
        
      

    

All of this requires some preprocessing (maybe in a trigger or so), but the queries should be much faster.

where somefield like '%a%' or somefield like '%b%'

      

        
        
        
      

    

select * from your_table where your_field like '%a%b%'

      

        
        
        
      

    

and be prepared to wait a while ...

Edit: Note that this pattern looks for "a" followed by "b" (possibly with other "stuff" in between) - rereading your question, it might not be what you wanted ...

You can use a cursor and temp table approach so that you don't have to do a full table scan every time. What this will do is to fill a temp table with all your keywords, and then each line in @letters XML

it will remove any records from the temp table. In the end, you only have entries in your temporary table that has each of your desired rows.

declare @letters xml

SET @letters = '<letters>
            <letter>a</letter>
            <letter>b</letter>
        </letters>'

-- SELECTING LETTERS FROM THE XML
SELECT Letters.l.value('.', 'nvarchar(50)') AS letter
FROM @letters.nodes('/letters/letter') AS Letters(l)

-- CREATE A TEMP TABLE WE CAN DELETE FROM IF A RECORD DOESN'T HAVE THE LETTER
CREATE TABLE #TempResults (keywordID int not null, keyWord nvarchar(50) not null)
INSERT INTO #TempResults (keywordID, keyWord)
SELECT employeeID, firstName FROM Employee

-- CREATE A CURSOR, SO WE CAN LOOP THROUGH OUR LETTERS AND REMOVE KEYWORDS THAT DON'T MATCH
DECLARE Cursor_Letters CURSOR READ_ONLY
FOR 
    SELECT Letters.l.value('.', 'nvarchar(50)') AS letter
    FROM @letters.nodes('/letters/letter') AS Letters(l)

DECLARE @letter varchar(50)
OPEN Cursor_Letters

FETCH NEXT FROM Cursor_Letters INTO @letter
WHILE (@@fetch_status <> -1)
BEGIN
    IF (@@fetch_status <> -2)
    BEGIN
        DELETE FROM #TempResults 
        WHERE  keywordID NOT IN 
        (SELECT keywordID FROM #TempResults WHERE keyWord LIKE '%' + @letter + '%')
    END
    FETCH NEXT FROM Cursor_Letters INTO @letter
END

CLOSE Cursor_Letters
DEALLOCATE Cursor_Letters

SELECT * FROM #TempResults

DROP Table #TempResults
GO

      

        
        
        
      

    

I am assuming you meant more or less what you said and want to find keywords in your table that “contain the letter“ a ”and the letter“ b. ”Some of the solutions here answer a different question.

To get keywords that contain the letters "a" and "b" (as opposed to those that contain any letter), you can use "ab" as in the following query:

select
  keyword
from myTable
where not exists (
  select Nums26.i from Nums26
  where Nums26.i <= len(<matchsetstring>) -- or your dialect equivalent for LEN()
  and keyword not like '%'+substring(<matchsetstring>,Nums26.i,1)+'%' -- adapt SUBSTRING to your dialect
);

      

        
        
        
      

    

The table named "Nums26" should have a column "i" (indexed for efficiency) that contains each of the values ​​1 through 26 (or more if you can try to match more letters). See below. Recommendations given by others are upper / lower case. However, if your collation is case sensitive, you cannot just specify "aAbB" here as yours, because this will ask for keywords that contain each of the four characters a, A, b and B. You can use UPPER and match "AB ", possibly.

create table nums26 (
  i int primary key
);
insert into nums26 values (1);
insert into nums26 select 1+i from nums26;
insert into nums26 select 2+i from nums26;
insert into nums26 select 4+i from nums26;
insert into nums26 select 8+i from nums26;
insert into nums26 select 16+i from nums26;