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
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
sql sql-server tsql
|
show 2 more comments
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
sql sql-server tsql
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record exampleassembly,c#,java,apache
is just one record or each word separated by comma is a record?
– Pedro Gaspar
Nov 22 '18 at 2:57
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
1
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41
|
show 2 more comments
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
sql sql-server tsql
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
sql sql-server tsql
sql sql-server tsql
edited Nov 23 '18 at 8:48
Sami
9,05831243
9,05831243
asked Nov 22 '18 at 2:34
Zahra NaeimpourZahra Naeimpour
46
46
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record exampleassembly,c#,java,apache
is just one record or each word separated by comma is a record?
– Pedro Gaspar
Nov 22 '18 at 2:57
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
1
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41
|
show 2 more comments
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record exampleassembly,c#,java,apache
is just one record or each word separated by comma is a record?
– Pedro Gaspar
Nov 22 '18 at 2:57
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
1
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record example
assembly,c#,java,apache
is just one record or each word separated by comma is a record?– Pedro Gaspar
Nov 22 '18 at 2:57
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record example
assembly,c#,java,apache
is just one record or each word separated by comma is a record?– Pedro Gaspar
Nov 22 '18 at 2:57
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
1
1
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41
|
show 2 more comments
3 Answers
3
active
oldest
votes
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
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see@Search
there? change the value or length as you like.
– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
|
show 12 more comments
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')
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
|
show 2 more comments
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))
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423109%2fhow-to-use-like-operator-in-sql-to-find-some-word-in-a-text-on-another-text%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
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
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see@Search
there? change the value or length as you like.
– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
|
show 12 more comments
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
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see@Search
there? change the value or length as you like.
– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
|
show 12 more comments
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
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
edited Nov 23 '18 at 9:07
answered Nov 22 '18 at 7:34
SamiSami
9,05831243
9,05831243
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see@Search
there? change the value or length as you like.
– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
|
show 12 more comments
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see@Search
there? change the value or length as you like.
– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
@ZahraNaeimpour Eh? are you sure? Can't you see
@Search
there? change the value or length as you like.– Sami
Nov 22 '18 at 13:03
@ZahraNaeimpour Eh? are you sure? Can't you see
@Search
there? change the value or length as you like.– Sami
Nov 22 '18 at 13:03
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
ty but how i can change it with my select query? i added my select query on this post
– Zahra Naeimpour
Nov 22 '18 at 18:10
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
@ZahraNaeimpour Check the updates
– Sami
Nov 22 '18 at 18:42
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
ty i updated my select query like your query but now it show all user record and search parameter does not take a effect plz check my update query in post
– Zahra Naeimpour
Nov 23 '18 at 8:08
|
show 12 more comments
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')
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
|
show 2 more comments
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')
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
|
show 2 more comments
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')
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')
edited Nov 22 '18 at 4:29
answered Nov 22 '18 at 3:28
dwir182dwir182
1,451619
1,451619
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
|
show 2 more comments
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
it is not work your query find any value start with that string but i need find any word is like my string words
– Zahra Naeimpour
Nov 22 '18 at 3:54
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
My bad.. Now check @ZahraNaeimpour.. :)
– dwir182
Nov 22 '18 at 3:56
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
i checked this query too but it is not work
– Zahra Naeimpour
Nov 22 '18 at 3:59
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
Not work are not good statement.. Tell what you are really facing.. And why it is not work..
– dwir182
Nov 22 '18 at 4:01
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
look my post again i add 2 pictures
– Zahra Naeimpour
Nov 22 '18 at 4:05
|
show 2 more comments
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))
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
add a comment |
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))
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
add a comment |
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))
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))
answered Nov 22 '18 at 7:50
Md. Mehedi HassanMd. Mehedi Hassan
176
176
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
add a comment |
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
Ty but search input it can be variable but you used fixed input string
– Zahra Naeimpour
Nov 22 '18 at 11:54
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
no problem, just pass your variable value @ZahraNaeimpour
– Md. Mehedi Hassan
Nov 25 '18 at 4:10
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53423109%2fhow-to-use-like-operator-in-sql-to-find-some-word-in-a-text-on-another-text%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Your question is not clear. Those words you want to check, are they stored on the database also or are they fixed? You want to return the records that contain any or all of those words? This record example
assembly,c#,java,apache
is just one record or each word separated by comma is a record?– Pedro Gaspar
Nov 22 '18 at 2:57
Please also specify (and tag the question) with the sql database platform you are using (eg. sql-server, oracle, mysql, etc)
– pcdev
Nov 22 '18 at 3:21
Also, what have you tried so far?
– umop apisdn
Nov 22 '18 at 3:28
i'm using sql server
– Zahra Naeimpour
Nov 22 '18 at 3:47
1
SQL Server has types designed for holding multiple values. The most obvious one of these is a table with multiple rows. Which is how these tags should be being stored. Rather than forcing them into a comma separated string.
– Damien_The_Unbeliever
Nov 22 '18 at 7:41