Check a word from filter table

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)

Check a word from filter table

Author

Topic

wizbay
Starting Member

3 Posts

Posted - 2010-11-27 : 05:56:25

Hi. I am trying create a stored procedure to check if a passed string parameter contains a bad word from badword table.

For example, I'd excute "sp_isthisbad @word='shityou'" and sp_isthisbad checks if the passed word is in the badword table. badword table consit of wordId int and word varchar(50). Since this table contains a row
5, "shit", it shoule be filtering the word "shityou"

Please anyone helps me with this issue.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 06:47:21

you can use PATINDEX or LIKE for achieving this

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MohanKandasamy
Starting Member

9 Posts

Posted - 2010-11-27 : 07:37:03

Hi,

Please use the below stored procedure as per your request

ALTER PROC SP_ISTHISBAD (
@word as varchar(40)
)
AS
SELECT * FROM BADWORD WHERE PATINDEX('%[ -Z]%',@word) > 0

in case if you want to pass any character, please check regular expression of Patindex.

Note : Please check fulltext search topic also your Knowledge.

Regards
Mohan Kandasamy

Mohan Kandasamy

wizbay
Starting Member

3 Posts

Posted - 2010-11-27 : 07:47:10

quote:
Originally posted by MohanKandasamy

Hi,

Thanks I'll try this.

Please use the below stored procedure as per your request

ALTER PROC SP_ISTHISBAD (
@word as varchar(40)
)
AS
SELECT * FROM BADWORD WHERE PATINDEX('%[ -Z]%',@word) > 0

in case if you want to pass any character, please check regular expression of Patindex.

Note : Please check fulltext search topic also your Knowledge.

Regards
Mohan Kandasamy

Mohan Kandasamy

wizbay
Starting Member

3 Posts

Posted - 2010-11-27 : 18:22:49

Hm..for some reason, this stored procedure doesn't work even if I pass an exact word.

quote:
Originally posted by MohanKandasamy

Hi,

Please use the below stored procedure as per your request

ALTER PROC SP_ISTHISBAD (
@word as varchar(40)
)
AS
SELECT * FROM BADWORD WHERE PATINDEX('%[ -Z]%',@word) > 0

in case if you want to pass any character, please check regular expression of Patindex.

Note : Please check fulltext search topic also your Knowledge.

Regards
Mohan Kandasamy

Mohan Kandasamy

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2010-11-27 : 18:52:08

I think the patindex should be using the variable passed as the condition, and the column from the badword table as the match...but this works. I apply UPPER to remove any option of case sensitivity issues..


Create table BADWORD (
		wordlist char(40) not null)

Go


Insert into BADWORD 
Select 'Cacayou' UNION
Select 'youCaca' 

GO

Create PROC SP_ISTHISBAD (
@word as varchar(40)
)
AS
SELECT UPPER(wordlist) 
FROM BADWORD 
WHERE Patindex('%'+@word+'%',wordlist)>0
GO

GO

Exec SP_ISTHISBAD 'Cacayou' --returns 1 record

Exec SP_ISTHISBAD 'Caca' --returns both records

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 02:42:18

it should be opposite


make this sp as below

Create PROC SP_ISTHISBAD (
@word as varchar(40)
)
AS
DECLARE @ReplacedWord varchar(max)
SELECT @ReplacedWord = CASE WHEN @ReplacedWord IS NULL THEN REPLACE(@word,wordlist,'') ELSE REPLACE(@ReplacedWord,wordlist,'') END
FROM BADWORD
SELECT @ReplacedWord
GO

and call it like

DECLARE @word varchar(max)
SELECT @word = 'you''re absolute Cacayou and a youCaca'
EXEC SP_ISTHISBAD @word

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-28 : 02:45:05

also just noticed you're using sp_ as name from procedure. Please dont name user stored procedures like this. Reason is below

see

http://sqlserverpedia.com/blog/sql-server-bloggers/stored-procedure-performance-using-%E2%80%9Csp_%E2%80%9D-prefix-%E2%80%93-myth-or-fact/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources