Using a word list with wildcards in SQL Server stored proc

I have a list of words that I want to exclude from some query results. The list of words is stored in a database table as a comma separated list. eg. bad, awful, worst. (Note: It isn't important that they are comma separated. They could just as easily be rows in a table).

I want to be able to utilise this list of words in a stored proc to ensure that keywords from this list don't appear anywhere in my search results. For example, given the following, how would I amend my query to ensure that all search results where filtered for words that appeared in my keywords exclusion list?

create table #keywords (column1 varchar(500)) insert #keywords values('bad, awful, worst') create table #testData (column1 varchar(100)) insert #testData values('This is bad for you') insert #testData values('This is good for you') insert #testData values('This is awful for you') insert #testData values('This is great for me') SELECT * FROM #testData WHERE column1 like '%you%' drop table #keywords drop table #testData

As you can probably guess, I want to ensure each of the keywords in the exclusion list are used within wildcards (%) to ensure that the word doesn't appear anywhere in the search results. I am aware of the dangers of SQL injection also, and would be keen to see a way of doing this that minimises any risk.

Any help is appreciated. This will be run on SQL Server 2005 and above.

EDIT - My main concern is not the comma separated list, but more the way to use the values it contains along with wildcards to exclude particular items from my query results.

-------------Problems Reply------------

First of all, I wouldn't store the keywords as CSVs. You're in a database, use it!

Try something like:

create table #keywords (column1 varchar(500))

insert #keywords values('bad')
insert #keywords values('awful')
insert #keywords values('worst')

create table #testData (column1 varchar(100))

insert #testData values('This is bad for you')
insert #testData values('This is good for you')
insert #testData values('This is awful for you')
insert #testData values('This is great for me')

select *
from #testData t
where column1 like '%you%'
and not exists (
select *
from #keywords k
where t.column1 like '%' + k.column1 + '%'
)

drop table #keywords

drop table #testData

Since you described that the keyword wordslist is there for a reason, I assume you are looking for a recusive solution. This is my surgestion:

DECLARE @keywords table (column1 VARCHAR(500))

INSERT @keywords VALUES('bad, awful, worst')

DECLARE @testData table (column1 VARCHAR(100))

INSERT @testData VALUES('This is bad for you')
INSERT @testData VALUES('This is good for you')
INSERT @testData VALUES('This is awful for you')
INSERT @testData VALUES('This is great for me')

;WITH cte(column1, b, e, keyword)
AS (
SELECT replace(column1, ' ', '') + ',' column1, 1 b, (charindex(',', column1 + ',')) e, cast('%' + substring(column1, 1, (charindex(',', column1 + ',')) - 1)+'%' as VARCHAR(50)) keyword
FROM @keywords
UNION ALL
SELECT column1, e + 2,charindex(',', column1, e + 1), cast('%' + substring(column1, e + 1, charindex(',', column1, e + 1) - e - 1)+'%' as VARCHAR(50))
FROM cte ch
WHERE charindex(',', column1, e + 1) > 0
)
SELECT *
FROM @testdata t
WHERE not exists (SELECT 1 FROM cte WHERE t.column1 like keyword)
and column1 like '%you%'

Category:sql Views:1 Time:2011-06-28

Related post

  • How to translate this C#-like code to an SQL Server stored proc 2011-05-30

    Below is pseudo-code for SQL Server stored procedure I need to write: int[] followers = (select FollowerID from StoreFollower where StoreId = @storeId) insert into Message (senderId, recipientId) values (@senderId, followers[0]) int rootMessageId = l

  • Find reference to (misspelt) object in sql server stored procs 2011-11-18

    I know that in a sql database stored proc, there is a misspelt (therefore non existant)sq object. What would be the best way to search the body of every stored proc in the database for this string? This would be the equivalent of find all references

  • SQL Server Stored Proc Argument Type Conversion 2008-10-22

    Suppose I have a bunch of varchar(6000) fields in a table and want to change those to text fields. What are the ramifications of the stored procedures whose arguments are of type varchar(6000). Does each stored procedure also need those argument data

  • SQL Server Stored Proc takes (much) longer to run than the same query executed from text in Management Studio 2009-04-17

    So here is a bit of a strange one... I have a stored proc that takes 40 seconds to run. I copy the contents of the stored proc to a new query window, change the 2 input parameters (which are both dates) so they are declared and set, and run the query

  • Programmatically replacing linked server references with local database references in SQL Server stored procs? 2009-07-09

    As a part of a scripted procedure I'm trying to programmatically update references to linked servers in stored procs. We have several references like this:- SELECT foo, bar FROM [Server].[Database].dbo.[Table] Which I would like to translate to:- SEL

  • optional parameters in SQL Server stored proc? 2009-11-27

    I'm writing some stored procs in SQL Server 2008, and wondered if the concept of optional input parameters is possible here? I suppose I could always pass in NULL for parameters I don't want to use, check the value in the stored proc, then take thing

  • sql server stored proc times out and query does not time out, why? 2010-02-24

    I have a stored proc on sql server 2008 that excepts a int parameter. It does call other stored procs and have nested quires. the problem i am facing is when i run the procedure from SQL server management studio it does not execute and times out. If

  • SQL Server Stored Procs include file statement 2010-07-19

    I don't think it is possible to do so what I would but I ask anyway. I've found that I include the same variables in the top of every Stored Proc I make. These variables are used for logging and error handling. They don't change between stored procs,

  • SQL Server Stored Proc - What is returned to a variable if no data? 2010-11-30

    If, using the following code in a MS SQL Server 2008 stored procedure: DECLARE @PROD_ID VARCHAR(20) SELECT @PROD_ID = MYTABLE.PROD FROM MYTABLE WHERE MYTABLE.DEVID = @DEVCODE DEVCODE does not exist, what will PROD_ID contain? I've tried printing it,

  • .net SQL Server stored procs wrapper class structure 2011-01-18

    I'm looking to write a C# SQL Server wrapper to call some stored procedures. If I was writing a single function I'd do something like the following (which I think is correct/proper): void RunStoredProc1(object arg1) { using(SqlConnection conn = new S

  • How can I step into a SQL Server stored proc from my C# code? 2011-01-19

    I am debugging some C# code that uses Ado.net to call a stored proc (TSQL) in SQL Server. How can I step into the stored proc? (I think I have seen this demoed by Microsoft staff, but can’t recall the 101 “magic” settings that are needed to get it to

  • SQL Server: Stored Proc input table variable workaround 2011-01-20

    I'm trying to find a good work around to not being able to use a table variable as an input to a stored procedure. I want to insert a single record into a base table and multiple records into a pivot table. My initial thought process led me to wantin

  • SQL Server stored proc performance 2011-02-15

    I have an ASP.NET app that has the following SQL query in a stored procedure: SELECT @result = COUNT(id) FROM course_tracking WHERE employee_number = @employee_number AND course_code = @course_code IF @result = 0 BEGIN INSERT INTO COURSE TRACKING END

  • Getting a Resultset from a SQL Server Stored Proc in Access 2011-06-05

    I've been 'lucky' enough to inherit an Access database that needs cleaned up tremendously. We're actually going to move off of Access and convert it into .NET/SQL Server. For now however we're retaining the Access database's UI and moving all the que

  • null valued top() parameter in SQL Server stored proc 2011-06-24

    I have a generic stored proc in my SQL Server 2005 DB It returns list of product items for use on a website and I want to make it more reusable across the site so I can use it to return specific quantities of products. The stored proc takes a num ite

  • SQL Server stored proc - need to return resultset before execution finishes 2011-10-27

    I have a competitions table with a 'WinningTime' field. Each day the first user to make a request after this time wins the competition, however they have a limited time to respond (say 10 min). If they don't respond within this time the competition i

  • SQL server stored proc "Procedure or function expects parameter, which was not supplied." 2012-01-04

    I've recently changed a couple of functions recently while debugging a cte query (have gotten help here). I just changed a couple of functions.. this got my query to work.. but now when my C# client code calls those functions through stored procs for

  • Any downside to "WITH RECOMPILE" for monthly SQL Server stored proc processes? 2008-12-04

    I think the question says it all. I have several monthly processes in stored procedures which take anywhere from a minute to an hour. If I declare them WITH RECOMPILE, an execution plan will be generated each time. If the underlying indexes or statis

  • Problem with search query in my Sql Server stored proc 2009-06-04

    I have the following stored proc :- SELECT Id, Name FROM FooBars WHERE CONTAINS(Name, 'FORMSOF(Tesaurus, @query)') Works fine when there is one word i the query: eg. foo* But it fails when I want to have more than one word which i'm trying to look fo

Copyright (C) dskims.com, All Rights Reserved.

processed in 0.177 (s). 11 q(s)