Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to write SQL query to find a word in string" Expand / Collapse
Author
Message
Posted Thursday, March 20, 2008 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 2, 2009 1:52 PM
Points: 14, Visits: 32
Hi,

I am trying to write a SQL statement to find related videos in a video site for a particular video. The TAG terms under a video goes as a query to this SQL statement to find related videos. Following SQL statement returns only the same video (single video) that has the exact tags. How do I change this to find all videos that has any word of the TAG string. Thanks. Norman

SELECT TOP 20 * FROM Videos WHERE isEnabled=1 AND isPrivate=0
AND title like '%Music Video Classic 80s %'
OR description like '%Music Video Classic 80s %'
OR tags like '%Music Video Classic 80s %'
ORDER BY date_added DESC
Post #472242
Posted Thursday, March 20, 2008 8:03 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 22, 2014 2:32 AM
Points: 1,260, Visits: 3,423
hello try this procedure maybe it helps you ...Cheers

CREATE PROCEDURE SEARCH
@SEARCH NVARCHAR(200)
AS
SELECT TOP 20 * FROM VIDEOS
WHERE isEnabled=1 AND isPrivate=0
AND (TITLE LIKE ('%' + @SEARCH% + '%') OR
DESCRIPTION LIKE ('%' + @SEARCH+ '%') OR
TAGS LIKE ('%' + @SEARCH+ '%')
);
GO

And you can try search like this:

EXEC SEARCH
BLLA BLLA BLLA


============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #472262
Posted Thursday, March 20, 2008 8:29 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, April 2, 2009 1:52 PM
Points: 14, Visits: 32
Hi Dugi, thanks for the quick reply. Here is the vb code that do this search. Isn't that same as yours?

Public Function Search_Related_Videos(ByVal search As String) As DataSet
Dim Query As String = "SELECT TOP 20 * FROM Videos WHERE isEnabled=1 AND isPrivate=0"
If Not search = "all" Then
Query = Query & " AND"
End If
If Not search = "all" Then
Query = Query & " (title like '%" & search & "%'"
Query = Query & " OR description like '%" & search & "%'"
Query = Query & " OR tags like '%" & search & "%')"
End If
'// Order by clause
Query = Query & " ORDER BY date_added DESC"

Return SqlHelper.ExecuteDataset(_constring, CommandType.Text, Query)
End Function
Post #472294
Posted Thursday, March 20, 2008 8:56 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 2:51 PM
Points: 3,840, Visits: 3,848
Check your exectution plan on this one and watch your indexes. Using LIKE with a leading wildcard will result in a table scan if you're not careful. From a high level glance, an index on isEnabled/isPrivate may help things here. It's hard to say not knowing the selectivity of those columns, your table row count, etc. Bottom line is, check your execution plan as this could lead to being a poor performer.



John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
Post #472317
Posted Thursday, March 20, 2008 12:41 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, November 30, 2010 11:03 AM
Points: 70, Visits: 31
ng949 (3/20/2008)
Hi,

I am trying to write a SQL statement to find related videos in a video site for a particular video. The TAG terms under a video goes as a query to this SQL statement to find related videos. Following SQL statement returns only the same video (single video) that has the exact tags. How do I change this to find all videos that has any word of the TAG string. Thanks. Norman

SELECT TOP 20 * FROM Videos WHERE isEnabled=1 AND isPrivate=0
AND title like '%Music Video Classic 80s %'
OR description like '%Music Video Classic 80s %'
OR tags like '%Music Video Classic 80s %'
ORDER BY date_added DESC


You should SPLIT the string and it will looks like:

SELECT TOP 20 * FROM Videos WHERE isEnabled=1 AND isPrivate=0
AND title like '%Music Video Classic 80s %'
OR description like '%Music Video Classic 80s %'
OR tags like '%Music%'
OR tags like '%Video%'
OR tags like '%Classic%'
OR tags like '%80s%'
ORDER BY date_added DESC

I think you can get my idea.

Cheers,
Gonzalo
Post #472489
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse