how to do tags searching in sql server

Question

4.50/5 (2 votes)

See more:

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

Posted 8-Feb-12 0:51am

shanalikhan

Updated 8-Feb-12 0:54am

v2

Add a Solution

3 solutions

Solution 1

C#

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

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

Posted 8-Feb-12 1:20am

Aniket Yadav

Solution 2

Using the [charlist] Wildcard

Posted 8-Feb-12 1:28am

uspatel

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RDBurmon · Accepted Answer · 2012-02-08T01:55:00

Solution 3

SQL

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'

SQL

DECLARE @Tags AS NVARCHAR(255)

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

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)

SQL

DECLARE @Tag nvarchar(255), @Pos int

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

IF REPLACE(@Tags, ',', '') &lt;&gt; ''
BEGIN
    WHILE @Pos &gt; 0
    BEGIN
        SET @Tag = LTRIM(RTRIM(LEFT(@Tags, @Pos - 1)))
        IF @Tag &lt;&gt; ''
        BEGIN

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

    END
END

Posted 8-Feb-12 1:55am

RDBurmon

Updated 11-Mar-12 21:59pm

v2

Comments

[no name] 11-Mar-12 14:27pm

Hi ,
can you please explain step by step what is happening which function are calling etc. in details , so i can edit it to my table, Im begineer in asp.net i have no concept of sql server function and other things except insertion , deletion , stored procedures etc...
Thanks

RDBurmon 12-Mar-12 4:00am

See my updated solutions .

Hope this helps if yes then accept and vote the answer
--Rahul D.

[no name] 12-Mar-12 7:44am

Thanks
how i can use this function using asp.net ,also how i can call this by stored procedure and return those rows of tables ?

RDBurmon 13-Mar-12 4:49am

ASP Code

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="_327067_how_to_do_tags_searching_in_sql_server._Default" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>

<br />
<br />
<asp:TextBox ID="TextBox1" runat="server" Width="243px">
<asp:Button ID="Button1" runat="server" onclick="Button1_Click" Text="Button"
Width="153px" />
<br />
<asp:GridView ID="GridView1" runat="server" >

</div>
</form>
</body>
</html>

C# Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;

namespace _327067_how_to_do_tags_searching_in_sql_server
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlConnection conn = null;

DataTable dt = new DataTable();
try
{

conn = new
SqlConnection("Server=MDT765;DataBase=TST;User Id=TestUser;Password=TestUser@123;Integrated Security=SSPI");
conn.Open();

SqlCommand cmd = new SqlCommand(
"FindElementsByTag", conn);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(
new SqlParameter("@Tags", TextBox1.Text.ToString()));

dt.Load(cmd.ExecuteReader());
GridView1.DataSource = dt;
GridView1.DataBind();

}
finally
{

}

}
}
}

Stored procedure

CREATE PROCEDURE FindElementsByTag
(
@Tags NVARCHAR(MAX)
)
AS
BEGIN
DECLARE @Tag nvarchar(255), @Pos int
DECLARE @TagSearch AS TABLE
(
[TYPE] NVARCHAR(50)
,[ID] Bigint
, [Tags] NVARCHAR(50)
)
SELECT @Tags=REPLACE(@Tags,' ',',')
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
INSERT INTO @TagSearch
SELECT * FROM TagSearch WHERE [Tags] Like '%' + @Tag + ',%' OR [Tags] Like '%,' + @Tag + '%'
END
SET @Tags = RIGHT(@Tags, LEN(@Tags) - @Pos)
SET @Pos = CHARINDEX(',', @Tags, 1)

END
END

SELECT distinct * FROM @TagSearch

END

Hope this helps if yes then vote the answer .
--Rahul D.

how to do tags searching in sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

how to do tags searching in sql server

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us