如何在SQL Server中执行标签搜索 [英] how to do tags searching in sql server

问题描述

我的表中有3个字段(类型，标识，标签). (标记仅在标记colunm中用逗号分隔)

当用户输入任何字符串时，当它与TAGS列的行中存在的任何标签匹配时，它应该分成几部分(按空格)，并且该字符串的每个部分都应尝试遍历tag列的所有行中的eachTAG.这些行应在datable中添加

我该怎么办?

假设

类型|| ID ||标签

typ1 || 1 || tag3，a，b，c

type3 || 2 || tag4，r，e
asf || 3 || tag5，y，u，q，e，w
aaa || 4 || e，f，g，h

当用户输入"tag3 r u"时

表格应仅通过从标签列中找到来发送ID 1,2,3的数据及其类型

谢谢

Hi,

there are 3 fields in my table ( type , id , tags ) . (tag(s) are separated by commas only in tags colunm)

when user enter any string , it should break into parts (by space) and the each part of that string should try to mtach through eachTAG in all rows of tags column , when it matches in any of the tag present in rows of TAGS column. those rows should be added in datable

how can i do it ?

Suppose

TYPE ||ID || Tags

typ1 ||1 || tag3,a,b,c

type3||2 || tag4,r,e
asf ||3 ||tag5,y,u,q,e,w
aaa ||4 || e,f,g,h

when user enter "tag3 r u"

the table shoud send these data of ID 1,2,3 and their types only by finding from tags column

Thanks

推荐答案

SELECT * FROM TableName WHERE Tags LIKE 'tag3%' OR Tags LIKE 'r%' OR Tags LIKE 'u%'

通过此查询，您将收到预期的记录.
使用此记录的for循环在datatable中获取此记录.

如果有帮助，请接受答案

From this query you will receive the expected records.
Fetch this records in datatable using the for loop for this records.

Accept the answer if it has helped you

使用[charlist]通配符

DECLARE @TagSearch  AS TABLE
(
 [TYPE] NVARCHAR(50)
 ,[ID] Bigint
 , [Tags] NVARCHAR(50)
 )

INSERT INTO @TagSearch
SELECT 'typ1',1,'tag3,a,b,c'
 Union all
 SELECT 'type3',2,'tag4,r,e'
 Union all
 SELECT 'asf',3,'tag5,y,u,q,e,w'
  Union all
 SELECT 'aaa',4,'e,f,g,h'

DECLARE @Tags AS NVARCHAR(255)

SELECT @Tags=REPLACE('tag3 r u',' ',',')

为以下代码创建一个表级函数，并在参数名称中提及@Tags
逐步说明代码
1)创建一个变量来存储每个标签(在这种情况下，我们的标签是tag3，r和u)
2)创建一个变量来存储位置，以将每个标签与字符串分开(在这种情况下，我们的字符串为"tag3 r u")
3)遍历字符串，并在逗号后提取每个标签，然后在表中搜索该标签(这里我们使用"@TagSearch"，但您可以提及您的表名)

Create one table level function for below code and mention @Tags as parameter name
Step by step explanation of code
1) Create one variable to store each tags (in this case our tags are tag3 , r and u)
2) Create one variable to store position to separate each tag from string(in this case our string is ''tag3 r u'')
3) Loop through the string and extract each tag after comma and then search that tag in table(here we are using "@TagSearch" but you can mention your table name)

DECLARE @Tag nvarchar(255), @Pos int

SET @Tags = LTRIM(RTRIM(@Tags))+ ','
SET @Pos = CHARINDEX(',', @Tags, 1)

IF REPLACE(@Tags, ',', '') <> ''
BEGIN
    WHILE @Pos > 0
    BEGIN
        SET @Tag = LTRIM(RTRIM(LEFT(@Tags, @Pos - 1)))
        IF @Tag <> ''
        BEGIN

            SELECT * FROM @TagSearch WHERE [Tags] Like '%' + @Tag + '%'
        END
        SET @Tags = RIGHT(@Tags, LEN(@Tags) - @Pos)
        SET @Pos = CHARINDEX(',', @Tags, 1)

    END
END

这篇关于如何在SQL Server中执行标签搜索的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！