How to use like operator in sql to find some word in a text on another text
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
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
You can use
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 + '%';
+-------------------------------------------------+ | Tags | +-------------------------------------------------+ | Analytics, Amazon Web Service, Active Directory | | Apple Safari, Analytics, Ajax | | Azure, Assembly, Appache | | BMC Remedy, Big Data, Ajax | +-------------------------------------------------+
STRING_SPLIT() function is avaliable only on 2016+ versions, so you need to create your own if you are not working on 2016+ versions.
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 + '%' )
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
You can use
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.