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

sql contains
multiple like in sql
sql like
sql like escape
sql like multiple words
sql like number
not like in sql
sql like regex

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


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

SQL LIKE Operator, Weekday WeekdayName Year Other Functions CurrentUser Environ IsDate IsNull IsNumeric There are two wildcards often used in conjunction with the LIKE operator: Here are some examples showing different LIKE operators with '%' and '_' wildcards: 6, Blauer See Delikatessen, Hanna Moos, Forsterstr. The SQL LIKE Operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: % - The percent sign represents zero, one, or multiple characters _ - The underscore represents a single character


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')

What does this <> symbol mean in SQL?, of the symbol “<>” is “not equal to”…. It's quite a simple this function that is used to compare values in the database table. You can eliminate the records which are not necessary. SQL Server LIKE operator overview. The SQL Server LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching.


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))

SQL: IN Condition, How do you use the IN operator in an SQL query? Solution. As mentioned already one approach of finding text that is stored within a larger set of text is by using the LIKE operator. The LIKE operator can be used in several different manners and there are some pros and cons of using this approach to limit data when issuing your queries.


How to Use LIKE in SQL: SQL Pattern Matching, to help reduce the need for multiple OR conditions in a SELECT, INSERT, UPDATE, or DELETE statement. Introduction to SQL LIKE operator. The LIKE operator allows you to search for a string of text based on a specified pattern. You can use the LIKE operator in the WHERE clause of any valid SQL statement such as SELECT , UPDATE or DELETE. SQL provides two wildcard characters that allow you to construct patterns.


How To Check If A String Contains A Substring In SQL Server, How do I find a specific word in a string in SQL? The LIKE Operator. The LIKE operator has already been discussed a bit, but the LIKE operator has several other syntax options that you can use. The LIKE operator uses the wildcard % character. The % character can be placed at the beginning, end or within your string value. Note that the % operator is for string or varchar values.


Overview of the SQL LIKE Operator, When you design a report or use BI or any reporting tool, the software is So, let's get started learning about SQL LIKE operator. Studio including T-SQL formatting, refactoring, auto-complete, text and data search, How to move SQL database files (MDF and LDF) to another location · SQL varchar data  Using LIKE, IN, BETWEEN, and wildcards to match multiple values in SQL Real-world data is often messy, so we need messy ways of matching values, because matching only on exact values can unintentionally filter out relevant data.