How to use like operator in sql to find some word in a text on another text

Question

I need to use like operator in sql query to check a text words in another text on database records to find any records like this text words for example:

my text is : ajax,net,apache,sql

and records like : 

assembly,c#,java,apache
ajax,pascal,c,c++
...

I need a query to find any rows has my text words

this picture is search input

and I'll find any rows has words in my search string

        SELECT
        dbo.tblProjects.id, dbo.tblProjects.prTitle, dbo.tblUsers.id AS UserID,tblUsers.nickname, 
        dbo.tblProjects.prTags, dbo.tblProjects.prDesc, dbo.tblProjects.prFaDate
    FROM
        dbo.tblUsers 
    INNER JOIN
        dbo.tblProjects ON dbo.tblUsers.id = dbo.tblProjects.UserID

    where (tblUsers.id=@userid)and(dbo.tblProjects.tags like @userskills + '%')

    order by dbo.tblProjects.id desc

Sami Update

ALTER procedure [dbo].[Dashboard_My_Skills_Projects]
(
  @userid bigint,
  @userskills nvarchar(100)
)
as
begin
    SELECT T.id, 
           T.prTitle, 
           U.id AS [UserID],
           U.nickname,
           T.prTags, 
           T.prDesc, 
           T.prFaDate

    FROM dbo.tblUsers U INNER JOIN dbo.tblProjects T ON U.id = T.UserID
    WHERE U.id = @userid
          AND
          T.tags IN(
                     SELECT tags
                     FROM dbo.tblProjects T1 CROSS JOIN 
                                  (
                                    SELECT Value 
                                    FROM STRING_SPLIT(@userskills, ',')
                                  ) TT
                     WHERE T1.tags LIKE '%' + Value + '%'
                   )


    order by T.id desc


end

tblUsers

tblProjects

Answer 1

You can use CROSS APPLY, STRING_SPLIT() and LIKE as

  CREATE TABLE T(
  Tags VARCHAR(100)
);

INSERT INTO T VALUES
('Analytics,Amazon Web Service,Active Directory'),
('BMC Remedy,Big Data,Ajax'),
('Azure,Assembly,Appache'),
('Azure,Assembly,Appache'),
('Azure,Assembly,Appache'),
('Azure,Assembly,Appache'),
('Apple Safari,Analytics,Ajax');

DECLARE @Search VARCHAR(45) = 'Active Directory,Ajax,Azure';

SELECT DISTINCT Tags
FROM T CROSS APPLY (SELECT Value FROM STRING_SPLIT(@Search, ',')) TT
WHERE Tags LIKE '%' + Value + '%';

Returns:

+-------------------------------------------------+
|                      Tags                       |
+-------------------------------------------------+
| Analytics, Amazon Web Service, Active Directory |
| Apple Safari, Analytics, Ajax                   |
| Azure, Assembly, Appache                        |
| BMC Remedy, Big Data, Ajax                      |
+-------------------------------------------------+

Demo

Note: STRING_SPLIT() function is avaliable only on 2016+ versions, so you need to create your own if you are not working on 2016+ versions.

---

UPDATE

CREATE TABLE Tags(
  Tags VARCHAR(100),
  UserID INT
);

CREATE TABLE Users(
  UserID INT,
  UserName VARCHAR(45)
 );

INSERT INTO Tags VALUES
('Analytics,Amazon Web Service,Active Directory', 1),
('BMC Remedy,Big Data,Ajax', 2),
('Azure,Assembly,Appache', 3),
('Azure,Assembly,Appache', 1),
('Azure,Assembly,Appache', 4),
('Azure,Assembly,Appache', 2),
('Apple Safari,Analytics,Ajax', 1);

INSERT INTO Users VALUES
(1, 'User1'),
(2, 'User2'),
(3, 'User3'),
(4, 'User4');

DECLARE @Search VARCHAR(45) = 'Active Directory,Azure',
        @UserID INT = 1;

SELECT U.UserID,
       U.UserName,
       T.Tags Skills
FROM Users U INNER JOIN Tags T ON U.UserID = T.UserID
WHERE U.UserID = @UserID
      AND
      T.Tags IN(
                 SELECT Tags
                 FROM Tags T1 CROSS JOIN 
                              (
                                SELECT Value 
                                FROM STRING_SPLIT(@Search, ',')
                              ) TT
                 WHERE T1.Tags LIKE '%' + Value + '%'
               )

Here is a live demo

and here is your stored procedure works just fine and as expected

Answer 2

First Create Function

Create FUNCTION fn_SplitADelimitedList
(
   @String NVARCHAR(MAX)
)
   RETURNS @SplittedValues TABLE(
   Value nvarchar(500)
   )
As
 BEGIN
      DECLARE @SplitLength INT
      DECLARE @Delimiter VARCHAR(10) 
      SET @Delimiter = ','

      WHILE len(@String) > 0
      BEGIN
           SELECT @SplitLength = (CASE charindex(@Delimiter, @String)
           WHEN 0 THEN
           datalength(@String) / 2
           ELSE
           charindex(@Delimiter, @String) - 1
           END)

       INSERT INTO @SplittedValues
            SELECT cast(substring(@String, 1, @SplitLength) AS nvarchar(50))
            WHERE
            ltrim(rtrim(isnull(substring(@String, 1, @SplitLength), ''))) <> '';
            SELECT @String = (CASE ((datalength(@String) / 2) - @SplitLength)
            WHEN 0 THEN
            ELSE
            right(@String, (datalength(@String) / 2) - @SplitLength - 1)
            END)
      END
 RETURN
END

Then Query Like this

DECLARE @yourtext NVARCHAR(400) = 'Apple, Active Directory'
SELECT 
LTRIM(RTRIM(c.Value)),
em.tags
FROM tblTest em
CROSS APPLY fn_SplitADelimitedList(em.tags) c
inner join 
(SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
SELECT CAST('<X>'+REPLACE(@yourtext, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a)) v 
on LTRIM(RTRIM(c.Value)) = LTRIM(RTRIM(v.DATA))

Answer 3

Like are to search for a specified pattern in a column..

And as @Pedro state in comment.. I hope you are not make this in just 1 record..

You can use

SELECT * 
FROM your_table
WHERE your_text LIKE '%ac%';

You can read more about Like operator in Here

EDIT :

You can use String_Split function

SELECT
   *
FROM
   table
WHERE
   EXISTS (SELECT 
              *  
           FROM 
              STRING_SPLIT(Tags, ',')  
           WHERE 
              value IN ('Active Directory', 'Ajax', 'Azure')