SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 How to search column by string keyword/phrase?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tudorH
Starting Member

13 Posts

Posted - 06/21/2013 :  11:16:53  Show Profile  Reply with Quote
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!

Edited by - tudorH on 06/21/2013 11:17:12

James K
Flowing Fount of Yak Knowledge

3578 Posts

Posted - 06/21/2013 :  11:20:47  Show Profile  Reply with Quote
SELECT * FROM YourTable WHERE DescriptionColumn LIKE '%Problem 1%'
In the general case, you might want to create a stored procedure with a parameter - for example, @SearchTerm. Then, the query would be:
SELECT * FROM YourTable WHERE DescriptionColumn LIKE '%'+@SearchTerm+'%'

Edited by - James K on 06/21/2013 11:21:03
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 06/21/2013 :  11:22:47  Show Profile  Reply with Quote
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
Go to Top of Page

tudorH
Starting Member

13 Posts

Posted - 06/21/2013 :  11:24:56  Show Profile  Reply with Quote
Darn, I completely forgot about Like! Was looking at doing something with CHARINDEX, thanks for the replies guys! I will give it a shot!
Go to Top of Page

tudorH
Starting Member

13 Posts

Posted - 06/21/2013 :  14:53:29  Show Profile  Reply with Quote
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?
Go to Top of Page

TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 06/21/2013 :  15:18:26  Show Profile  Reply with Quote
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
Go to Top of Page

tudorH
Starting Member

13 Posts

Posted - 06/24/2013 :  11:25:05  Show Profile  Reply with Quote
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

Edited by - tudorH on 06/24/2013 11:26:38
Go to Top of Page

ScottPletcher
Constraint Violating Yak Guru

USA
362 Posts

Posted - 06/25/2013 :  13:53:25  Show Profile  Reply with Quote

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 %'
Go to Top of Page

tudorH
Starting Member

13 Posts

Posted - 06/26/2013 :  13:52:16  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000