March 13, 2006 at 1:34 pm
I need some help with a stored procedure I am working on and hope someone has some good advise!
I have a database of 'bad/blacklisted' strings and I want to pass in a value to a procedure and return a row if the value passed in is in the table. At the very beginning this is easy (just selecting against table with contains clause) but this gets more difficult if the string contains other characters too.
The table just contains two columns, primarykey1, and blacklistedstring. Lets say the only row in the table is (1, Crap). So if I pass in the value of crap it would return one row right?
select * from blacklistedtable where blacklistedstring like '%Crap%'
Where I run into problems is if the value passed into the procedure is Crap1
select * from blacklistedtable where blacklistedstring like '%Crap1%' returns 0 rows.
Any suggestions? Please let me know!
-Chad
March 13, 2006 at 1:47 pm
I should also note that I have tried using padindex/charindex but this still doesn't help much...
Example:
select count(*) from BLacklist
where CHARINDEX('crap', BlackListResponse) > 0
does return a value
select count(*) from BLacklist
where CHARINDEX('crap1', BlackListResponse) > 0
doesn't return a value.
March 13, 2006 at 1:58 pm
Have a look at http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=4&messageid=175213
He provides a bunch of user defined functions that may help you out.
March 13, 2006 at 2:52 pm
You might want to try something like:
declare @astring varchar(20), @loop int, @maxlen int, @SearchString varchar(20)
set @astring = 'crap1'
set @maxlen = len(@astring)
set @loop = 1
while @loop <= @maxlen
begin
select @SearchStrign = '%'+left(@astring,@loop)+'%'
--select * from blacklistedtable where blacklistedstring like @SearchString
set @loop = @loop + 1
end
This will build a string down to one character, of course you can set this down to maybe 3 characters or some other limit. Put the results in a temp table and remove duplicates.
Hope this helps.
George
March 13, 2006 at 5:06 pm
Well this was closer (and thanks for your help!) but still this has its own problems. For example this still returns one row if the string was 'carry was playful' because it ends up creating the words crap out of that sentence. What I am wanting it something like the phrase 'This is a bunch of crap that also has other text in it' and for it to say 'HEY! YOU CAN'T HAVE THE WORD CRAP IN THERE!'
declare @astring varchar(100), @loop int, @maxlen int, @SearchString varchar(100)
declare @mycount int
set @astring = 'carry was playful'
set @maxlen = len(@astring)
set @loop = 1
while (@loop <= @maxlen)
begin
select @SearchString = '%'+left(@astring,@loop)+'%'
set @mycount = (select count(*) as [MyCount] from BLacklist where blackListResponse like @SearchString AND InActive=0)
set @loop = @loop + 1
IF @mycount > 0
BREAK
ELSE
CONTINUE
END
Select @mycount as [Response]
Any other ideas? Maybe this is something I am going to have to end up coding and calling the procedure for each row in the table?
March 13, 2006 at 5:41 pm
select count(*) from BLacklist
where CHARINDEX(BlackListResponse, 'crap1 or what ever crap they may send') > 0
_____________
Code for TallyGenerator
March 13, 2006 at 5:48 pm
Oh, WOW! I guess before I had tried:
select count(*) from BLacklist
where CHARINDEX('crap1 or what ever crap they may send', BlackListResponse) > 0
but not the other way around!
Sweet!
Thanks for everyones help!
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply