sql server - SQL identify whether a word in nvarchar variable is listed in lookup table -

sql server - SQL identify whether a word in nvarchar variable is listed in lookup table -

i writing procedure automatically overwrite profane words entered user on form , replace text asterisks if word in question in lookup database.

for sake of example, let's assume flubber rude word in our profanity table , need flag when it's entered word on own. 'flubber' needs flagged inappropriate, 'flubbermost' fine.

user enters: watching flubber.

what want update info in field with: watching *******. or variation of that, perhaps f****r.

i've tried using contains (and i'm trying identify whether have profane word @ stage)

declare @string nvarchar(4000) set @string = n'i watching flubber' select myword mylookup contains(myword,@string)

but next message

msg 7630, level 15, state 3, line 4 syntax error near 'really' in full-text search status 'i watching flubber'.

any help gratefully received.

edit:

following help ahiggins i've added total text index , next code works well:

declare @string nvarchar(4000) declare @matchedword nvarchar(100) declare @returnstring nvarchar(4000) set varstring = 'i watching flubber' select varmatchedword = myword mylookup freetext (descr,@string) print convert(varchar(4000),@matchedword)

and returns info when whole word found - perfect.

onto sec part of question: what's best way of substituting word 'flubber' '*******' please?

given new requirements, i'm going point towards this answer suggests (strongly) utilize total text search functionality in sql server. if unavailable, though, can take performance nail of doing , utilize next code:

select myword, @string searchphrase mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%'

full illustration sample data:

declare @mylookup table (myword varchar(20)) insert @mylookup (myword) values ('flubber') declare @string nvarchar(4000) set @string = n'i watching flubbers' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%' set @string = n'i watching flubber.' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%' set @string = n'i watching flubber, weird?' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%' set @string = n'i watching flubber movie' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%' set @string = n'i watching flubber!' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%' set @string = n'i watchingflubber' select myword, @string searchphrase @mylookup '.' + @string + '.' '%[^a-z]'+myword+'[^a-z]%'

edit: talk moving target ... i've taken code comment (using total text search) , printed off homecoming string replace word asterisks.

note if word shows multiple times in same search string, replace instances. don't have access instance total text search enabled, you'll have confirm working expected.

declare @string nvarchar(4000) declare @matchedword nvarchar(100) declare @returnstring nvarchar(4000) set @string = 'i watching flubber' select @matchedword = myword, @returnstring = replace(@string, myword, replicate('*', len(myword))) mylookup freetext (descr,@string) print convert(varchar(4000), @matchedword) print convert(varchar(4000), @returnstring)

sql sql-server