Need a logic
-
i have a message table and a token table i want to search the message based on tokens
DECLARE @message AS TABLE ([Description] VARCHAR(400))
INSERT INTO @message([Description])
VALUES('duplicate')
INSERT INTO @message([Description])
VALUES('Record already deactivated by another user')
INSERT INTO @message([Description])
VALUES('Record already activated by another user')
INSERT INTO @message([Description])
VALUES('Record already terminated by another user')
INSERT INTO @message([Description])
VALUES('Record already modified by another user')
INSERT INTO @message([Description])
VALUES('Record deactivated already by another user')
SELECT * FROM @message
DECLARE @Keyword AS TABLE (id int,Token VARCHAR(20))
INSERT INTO @Keyword
VALUES(1,'already'),(1,'Deactivated'),(2,'modified'),(3,'activated')
/*
DECLARE @Keyword AS TABLE (id int,Token VARCHAR(20))
INSERT INTO @Keyword
VALUES(1,'already'),(1,'Deactivated'),(2,'already'),(3,'activated'),(3,'already')
*/
i want to search the message based on the keyword table
DECLARE @id INT =2
SELECT * FROM @message m
WHERE
EXISTS (SELECT 1 FROM @Keyword k
WHERE k.id =@id AND
m.[Description] LIKE '%'+ k.Token+'%')
i get the expected result, but when I gave the @id value as 1 i am expecting only the matching records
see below
DECLARE @id INT =1
SELECT * FROM @message m
WHERE
EXISTS (SELECT 1 FROM @Keyword k
WHERE k.id =@id AND
m.[Description] LIKE '%'+ k.Token+'%')
/* Expected output
'Record already deactivated by another user'
'Record deactivated already by another user'
*/
I know why it return more than two rows, may be my logic is wrong here, any other logic, or is it possible any new way ?
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Try this, using Full-text search:
CREATE TABLE tblmessage (ID INT IDENTITY(1,1) NOT NULL,[Description] VARCHAR(400))
INSERT INTO tblmessage([Description])
VALUES('duplicate')
INSERT INTO tblmessage([Description])
VALUES('Record already deactivated by another user')
INSERT INTO tblmessage([Description])
VALUES('Record already activated by another user')
INSERT INTO tblmessage([Description])
VALUES('Record already terminated by another user')
INSERT INTO tblmessage([Description])
VALUES('Record already modified by another user')
INSERT INTO tblmessage([Description])
VALUES('Record deactivated already by another user')
CREATE UNIQUE INDEX PK_tblmessage_ID ON tblmessage(ID)
CREATE FULLTEXT CATALOG fttest AS DEFAULT;
CREATE FULLTEXT INDEX ON tblmessage([Description]) KEY INDEX PK_tblmessage_ID
DECLARE @Keyword AS TABLE (id int,Token VARCHAR(20))
INSERT INTO @Keyword
VALUES(1,'already'),(1,'Deactivated'),(2,'modified'),(3,'activated')
DECLARE @id INT = 1
DECLARE @message varchar(255)
SELECT @message = (STUFF((SELECT ' AND ' + Token FROM @keyword WHERE ID = @id FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1,4,''))
SELECT * FROM tblmessage m
WHERE contains (m.[Description], @message)
DROP TABLE tblmessage
-
Thanks for the response, well that answer was promoted by my seniors, but the trouble is we hold lots of columns in a same table, and tons of tables like this, so create full text index and maintain for all that tables and columns is a tedious and complex job, so he takes me to get a more honest answer to resolve this without using a full text index or REGEX or CLR, so on the whole I need to find a solution in TSQL, is it possible to do this kind of stuff in TSQL?
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Simplest way to fix this, remove the token 'already' as this appears to exist in all the messages. It doesn't make any sense to me to search for 'already' and 'deactivated' together when what you are looking for is simply 'deactivated'. You aren't looking for 'already' and 'activated' or 'already' and 'modified'.
-
Thanks for your info, those are some messages to show the user in the front end, out application is data driven that's why we have such messages in our Tables, more over we just do this for remove unwanted data in all of our Customers,
That's only part of the data, I believe I have already told that we have dozens of tables like this
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
Bear in mind that I don't think this is a good idea, but this should do what you want: -DECLARE @id INT =1;SELECT *
FROM @message m
WHERE EXISTS (SELECT 1
FROM (SELECT STUFF((SELECT '%' + Token
FROM @Keyword k
WHERE k.id =@id
FOR XML PATH(''), TYPE
).value('.','NVARCHAR(MAX)'),1,1,''
)
)a(tokens)
WHERE m.[Description] LIKE '%'+ a.tokens+'%');
--EDIT--Jeez, no it won't. Ordering of the tokens would cause issues. The post below by "gbritton1" looks far better.
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Your problem is that the token 'already' is matched by all rows in @message except the first one. So, all rows are returned except the first.
I think that you want to only return rows where all of the tokens match. That's a harder problem. Here's one approach that does what you want:
DECLARE @id INT =1
SELECT m.description
FROM @message m
Where 0 < all (
select patindex('%'+ k.token +'%', m.Description)
from @keyword k
where k.id = @id)
-
1. You have anerror in your keyword table, both 'already' and 'Deactivated' have id=1
2. Like will not work as 'activated' keyword will find 'activated' and 'Deactivated'
Solution using splitter
SELECT m.[Description]
FROM @message m
CROSS APPLY dbo.DelimitedSplit8K(m.[Description],' ') s
JOIN @Keyword k ON k.Token = s.Item AND k.id IN (1,2)
GROUP BY m.[Description] HAVING COUNT(DISTINCT k.id) = 2
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks , but it only return one record i need both records, i think we are close to the solution, so it is possible
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
i am also found one but it is not good even though i share it
DECLARE @id INT =1;
WITH CteTokens AS(
SELECT ROW_NUMBER() OVER (ORDER BY id) WordCount, Token
FROM @Keyword WHERE id = @id
),
REcursive_Cte AS(
SELECT 1 AS WordCount, m.[Description]
FROM @message m
WHERE EXISTS (SELECT 1
FROM CteTokens c
WHERE m.[Description] LIKE '%'+ c.token+'%' AND c.wordcount =1)
UNION ALL
SELECT m.WordCount +1 AS Worcount, m.[Description]
FROM REcursive_Cte m
WHERE EXISTS (SELECT 1
FROM CteTokens c
WHERE m.[Description] LIKE '%'+ c.token+'%' AND c.WordCount =m.WordCount+1)
)
SELECT * FROM REcursive_Cte WHERE Wordcount = (SELECT COUNT(*) FROM @Keyword WHERE id = @id)
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
thank you people for you time and effort, you people made my day
Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url] -
I like the solution above from gbritton1, but nother way of doing which is probably less efficent but perhaps also easier to understand would be
select m.description from @message mwhere not exists (select 1 from @keyword k where k.id = @id and m.Description not like '%'+k.token+'%')
Tom
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply