Click here to Skip to main content
14,739,403 members
Please Sign up or sign in to vote.
4.50/5 (2 votes)
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
Updated 8-Feb-12 1:54am
v2

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
   
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',' ',',')




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
   
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.

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900