SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
 
User Name:
Password:
Remember me
Go Back   ASP Free ForumsDatabaseSQL Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread ASP Free Forums Sponsor:
  #1  
Old September 28th, 2003, 04:48 PM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Unhappy problems with LIKE query

Hi

I have an access database which has a table full of users and their scores and another table full of unacceptable user names.

If a user tries to enter their name as "damn this game" i want the asp to spot that 'damn' is listed in my table (tblSwear) and replace the entire name with "anon".

I read on 4guys from rolla that when using a like statement if u place % around either side of the word u are comparing then it should allow for other characters appearing around the word.

ie '%damn%'
would find "blah-damn-blah"

but for some reason my code below will only detect a user entering purely "damn" despite my use of the % symbol and it will not detect blahdamnblah"

Code:
dim thename
thename=request("rude")
sql = "SELECT * FROM tblSwear WHERE tblSwear.word LIKE '%" & thename & "%'"
'Select * from Tablename where (Firstname) LIKE '%" & FirstNameVar & "%'"
rs.open sql,myconn,3,3
if rs.recordcount > 0 then
response.Write("anon")
else
response.Write(thename)
rs.close
end if


I keep staring at my code and i cant see what the mistake is, i have attached it and the database and files in case any1 has the time to give it a glance.

I will be so grateful for any help.
Attached Files
File Type: zip scoreboard.zip (9.4 KB, 377 views)

Reply With Quote
  #2  
Old September 28th, 2003, 04:56 PM
Soky's Avatar
Soky Soky is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Bowling Green KY
Posts: 77 Soky User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 49 m 15 sec
Reputation Power: 6
Send a message via Yahoo to Soky
try %'damn'%


PHP Code:
 LIKE %'" & thename & "'%


NOTE: You will likely do better placing your bad words in an array and then looping through them with the replace function in VBScript/ASP

Last edited by Soky : September 28th, 2003 at 05:02 PM.

Reply With Quote
  #3  
Old September 28th, 2003, 05:01 PM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
hi Soky

unfortunately it didnt work, thanks anyway.

ScoobyDoo

Reply With Quote
  #4  
Old September 28th, 2003, 05:02 PM
Soky's Avatar
Soky Soky is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Bowling Green KY
Posts: 77 Soky User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 49 m 15 sec
Reputation Power: 6
Send a message via Yahoo to Soky
damn

Reply With Quote
  #5  
Old September 28th, 2003, 05:04 PM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0

Reply With Quote
  #6  
Old September 28th, 2003, 05:40 PM
Soky's Avatar
Soky Soky is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Bowling Green KY
Posts: 77 Soky User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 5 h 49 m 15 sec
Reputation Power: 6
Send a message via Yahoo to Soky
Sorry man... I don't have time to look into it deeper. It looks like your LIKE statement should work... perhaps it is curser related? I'm not sure.

Give it a day or two and I have no doubt someone here will come to your rescue.

Reply With Quote
  #7  
Old September 28th, 2003, 05:52 PM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
thanks anyway, ur response was kinda swift, so u get credit for that!

Reply With Quote
  #8  
Old September 28th, 2003, 06:40 PM
Doug G Doug G is offline
Grumpier Old Moderator
ASP Free God 11th Plane (10000 - 10499 posts)
 
Join Date: Sep 2003
Posts: 10,143 Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level)Doug G User rank is First Lieutenant (10000 - 20000 Reputation Level) 
Time spent in forums: 3 Weeks 4 Days 23 h 29 m 58 sec
Reputation Power: 181
You could try using * instead of % for Access

Reply With Quote
  #9  
Old September 28th, 2003, 06:56 PM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
hi Doug

nope, that didn't work either, but thanks for trying!

Reply With Quote
  #10  
Old September 29th, 2003, 12:23 AM
Onslaught Onslaught is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 34 Onslaught User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 44 sec
Reputation Power: 6
I think your doing this in reverse order.
You have a table full of swear words and searching against them, but if the user enters in damn this game into the field, they in your sql statement your doing this:
sql = "SELECT * FROM tblSwear WHERE tblSwear.word LIKE '%dame this game%'"

This is the reason why you aren't getting a match.
I would suggest you use Soky's suggestion on using an array with replace to find and replace instances of swear words.

Reply With Quote
  #11  
Old September 29th, 2003, 05:37 AM
scoobydoo scoobydoo is offline
Registered User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 10 scoobydoo User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
OK.

I've been on a hunt and found this code which i have modified to the below. It used to replace all but the first and last characters of the detected rude word with an asterisk but now it just replaces the entire phrase with 'anon' great!

The only thing is I wanted all the rude words to be in a database so that a non webdeveloper can add additional words as and when some clever d!ck uses them using a simple form. Then upon the new word being added the swear word filter would be run on the contents of the database to find the offending item. Is there a way of modifying what i have to achieve this?

Heres is the code i mentioned, though i think it is now an asp question not an sql one :

Code:
'this function parses a string, filtering out the swear words defined by yourself
Function fnParseSwear(strSwear)
'Declare our variable
	Dim rex, match, matches
'Create an instance of the regexp object
	Set rex = New regexp
	rex.Pattern = "damn|heck|darn"
'global determines whether we search the whole string, or just
'be satisfied w/ the first hit we come across.
	rex.Global = True
'ignorecase, self explanatory
	rex.IgnoreCase = True
'execute the regexp on our text string
    Set matches = rex.Execute(strSwear)
'go thru the matches (swear words), and replace them w/ asterisks in the middle, 
'this is the bit i modified sot hat rather than replacing just the matches it replaces the whole lot
    For Each match in matches
        strSwear = Replace(strSwear, strSwear, "Anonymous")
    Next
'set our function equal to our parsed string...
    fnParseSwear = strSwear
'Clean up
	Set rex = Nothing
End Function

dim person
person = fnParseSwear(Request("person"))

Reply With Quote
  #12  
Old September 29th, 2003, 09:05 AM
Onslaught Onslaught is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 34 Onslaught User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 44 sec
Reputation Power: 6
you can build your pattern string through the database. Just select the words to replace and join them into a string in the format that you need.

Reply With Quote
  #13  
Old September 30th, 2003, 07:55 AM
m_lazor m_lazor is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Luxembourg
Posts: 156 m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
LIKE query

What about using this piece of code:

'*** Split the text the guy entered in (hopefully) distinguishable words
TextArray = Split(txtInputField, " ")

SQL = "SELECT * FROM tblSwear WHERE "

'*** Cycle thru the array. ***
For I = LBound(TextArray) To UBound(TextArray)
SQL = SQL & "tblSwear.word LIKE '" & TextArray(I) & "*' OR "
Next

'*** Cut off the last 'OR'. ***
SQL = Left(SQL, Len(SQL) - 3)


Does the trick?

Reply With Quote
  #14  
Old September 30th, 2003, 08:39 AM
Onslaught Onslaught is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Posts: 34 Onslaught User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 44 sec
Reputation Power: 6
How are you going to know which one of the possible words entered matched the swear word to replace?

Reply With Quote
  #15  
Old September 30th, 2003, 08:54 AM
m_lazor m_lazor is offline
Contributing User
ASP Free Newbie (0 - 499 posts)
 
Join Date: Sep 2003
Location: Luxembourg
Posts: 156 m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level)m_lazor User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 7
Well, it should be contained in the recordset, shouldn't it? The table is called tblSwear, so hopefully it's not a dictionnary of non-offending words.

SELECT * FROM tblSwear WHERE tblSwear.word LIKE "*****" OR ...

should result in

rs!word = "****ty"

whereas stuff like "Honey" does not find records

Quick and dirty would be to re-scan the possible words
-----------------------
if not rs.eof
rs.movefirst
For I = LBound(TextArray) To UBound(TextArray)

'*** Do the search backwards. ***
if InStr(rs!word, textarray(i)) > 0 Then MsgBox "Gotcha"
rs.movenext
next
end if
-----------------------
I agree it's not nice but I've got some more stuff to do...

Ah, just another one:
send the queries one-by-one and not in one shot with OR operators. This should give you full control as one word may have several occurences: "****", "****ty", "****head", .... (what do I know?)

BTW can you see the full text or do you only see the "*" when offending words are inserted as an example?

Martin

Reply With Quote
Reply

Viewing: ASP Free ForumsDatabaseSQL Development > problems with LIKE query


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump