-3

This is quite difficult to explain but, I have a table (tenant) with a column called occupant. I want to know if the value in the column fully/partially matches the value in another table (offensive) with only one column called bad_words.

i.e.

tbl_Tenant.Occupant
------------------

Julia f*t
P*y Girl
Gary A*e

tbl_Offensive.bad_words
---------
s*t
f*t
p*y
a*e

I know the following query is incorrect but I'm trying to do something like this but cant think of the correct way to script it.

select * from tbl_tenant t
where t.occupant like '%(select o.bad_words from tbl_offensive o)%'

Can someone please help!

ChrisF
  • 134,786
  • 31
  • 255
  • 325
Abdul Kahar
  • 43
  • 2

2 Answers2

2

This thread is very similar: SQL: Join tables on substrings

Translated for your tables:

Select * 
From tbl_Tenant t 
Inner Join tbl_Offensive As o 
   on ( o.bad_words like '%'+t.Occupant+'%' )
Community
  • 1
  • 1
bgoerdt
  • 162
  • 1
  • 7
  • Hi @ArdalanShahgholi, Thanks for your reply. I am searching through circa 6 million records. Is there another optimized version of this code as it's taking forever to run. Also Is there a way to just retrieve records if there is a word for word match i.e. 'John Pussy massod'. This should be returned because 'pussy' was matched. Not because 'ass' was found in 'massod'. Your code finds both 'pussy' and 'massod' – Abdul Kahar Dec 03 '14 at 11:07
  • I slight ly changed the above code to: Select * From tbl_Tenant t Inner Join tbl_Offensive As o on ( o.bad_words like '% '+t.Occupant+' %' ). This worked wonders. thanks to all who helped. I will vote your answers – Abdul Kahar Dec 03 '14 at 11:16
1

It most be work :

Select * 
From tbl_Tenant As T
    cross join tbl_Offensive As O
Where ( O.bad_words like '% ' + T.Occupant + ' %' )
  Or
  ( O.bad_words like T.Occupant + ' %' )
  Or
  ( O.bad_words like '% ' + T.Occupant )
Ardalan Shahgholi
  • 11,967
  • 21
  • 108
  • 144