How to search column by string keyword/phrase? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

How to search column by string keyword/phrase?

Author

Topic

tudorH
Starting Member

13 Posts

Posted - 2013-06-21 : 11:16:53

Hi,

I am looking for a way to query a certain column (Description) by some string. So for example say my table contains Emails as records and the Description column contains email descriptions. Is there a way for me to be able to type something like "problem 1" and for the query to return all Email records where "problem 1" appears in the Email Description column?

Is this something I can do in SQL? I have the ability to use either 2008 or 2012 if that makes a difference.

Thanks for any ideas!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-21 : 11:20:47

[code]SELECT * FROM YourTable WHERE DescriptionColumn LIKE '%Problem 1%'[/code]In the general case, you might want to create a stored procedure with a parameter - for example, @SearchTerm. Then, the query would be:[code]SELECT * FROM YourTable WHERE DescriptionColumn LIKE '%'+@SearchTerm+'%'[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-21 : 11:22:47

yep you can use like


SELECT Email
FROM Table
WHERE Description LIKE '% problem 1 %'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

tudorH
Starting Member

13 Posts

Posted - 2013-06-21 : 11:24:56

Darn, I completely forgot about Like! Was looking at doing something with CHARINDEX, thanks for the replies guys! I will give it a shot!

tudorH
Starting Member

13 Posts

Posted - 2013-06-21 : 14:53:29

Alright, gave it a shot and it works! But its very slow! We have a few thousand records in the table and it takes about 1.5 minutes to find all email descriptions which contain the search term. Is there any way to speed this up or to use something else?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2013-06-21 : 15:18:26

90 seconds to scan only a few thousand rows? Can you post your actual sql statement? what is the datatype of the column you are searching? and how long are the longest values?

Be One with the Optimizer
TG

tudorH
Starting Member

13 Posts

Posted - 2013-06-24 : 11:25:05

TG, The datatype of the column is nvarchar(max). Basically this column stores the body of emails so the size really varies. Since a lot of emails coming in have HTML code embedded, I have a function which goes through and strips the HTML tags, hence the udf_StripHTML in the following statement:

SELECT dbo.ActivityPointerBase.Subject, dbo.ActivityPointerBase.CreatedOn, dbo.udf_StripHTML(dbo.ActivityPointerBase.Description) AS 'Description'
	FROM dbo.ActivityPointerBase INNER JOIN dbo.IncidentBase
	ON dbo.ActivityPointerBase.RegardingObjectId=dbo.IncidentBase.IncidentId
	WHERE dbo.udf_StripHTML(dbo.ActivityPointerBase.Description) LIKE '% help %'

Thanks for the help!

EDIT: would it be best to look into something like Full Text Search - http://msdn.microsoft.com/en-us/library/ms142571.aspx

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2013-06-25 : 13:53:25

Scalar functions can be very slow; probably particularly so in this case, as I'm guessing the function code itself is not that efficient.

The html won't affect this SELECT, so leave the function out of the WHERE and just put it in the SELECT colunmn list:

SELECT dbo.ActivityPointerBase.Subject, dbo.ActivityPointerBase.CreatedOn, dbo.udf_StripHTML(dbo.ActivityPointerBase.Description) AS 'Description'
FROM dbo.ActivityPointerBase INNER JOIN dbo.IncidentBase
ON dbo.ActivityPointerBase.RegardingObjectId=dbo.IncidentBase.IncidentId
WHERE dbo.ActivityPointerBase.Description LIKE '% help %'

tudorH
Starting Member

13 Posts

Posted - 2013-06-26 : 13:52:16

Thanks ScottPletcher, that helps a bit but still quite slow. Not fast enough to warrant using this query. I think I will investigate the Full Text Search and give that a shot as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources