How to use like to search a text field

I have two tables.  One table has three fields that may contain the words of another field in another table.

For example....

Table A/ Field1 (Description) - Mary had a lamb. Field2 (Action) fast white. Field3 (Accent) - Curly Wool.
Table B/Field (Category) - Lamb belongs to Mary.

How do write a query to return all rows in table A that have 'mary' or 'lamb' in the table B/category field? I used the wildcards '%Mary%' but received numerous erroneous rows.

To do this correctly, I should have one row returned from table A because field 1 (Description) has the words Mary & Lamb although only one word is needed.

Thank You


Glen_DAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Terry WoodsIT GuruCommented:
Are you sure you're joining correctly? I would have thought like '%Mary%' should work just fine.
0
Glen_DAuthor Commented:
The text fields in table a have almost a 256 character count.  The text field in table b (only field) has the categories set correctly, e.g. a separate row for each category.

I didn't use a join but only listed the tables as:

from table a a, table b b
where a.description like '%Mary%' or
          a.action like '%Mary% or
          a. accent like '%Mary%


I have numerous categories to compare with the data in table a.  Isn't there a way to write a query to compare text strings in two different tables automatically without having to use each separate word?


0
Terry WoodsIT GuruCommented:
The way you've omitted the join in the query, you're asking for a result as follows:

Return every row in table b once for each row in table a where description is like '%Mary%' or...

This means you will get every row in table b twice if you have 2 rows in table a picked up with the given filter. It's a cartesian product - probably not what you want?
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Terry WoodsIT GuruCommented:
You need to join the tables to get a sensible result, probably.
0
Glen_DAuthor Commented:
There is nothing to join on....

Join on a 'Like' statement?
0
Terry WoodsIT GuruCommented:
Are you interested in getting a partial match - eg where the category is "Lamb belongs to Mary" do you want the query to figure out that "lamb" and "mary" (or "belong"?) are words we're interested in, then look in table A for those?
0
Glen_DAuthor Commented:
yes...a partial match is exactly what I'm looking for

Thank You
0
Terry WoodsIT GuruCommented:
Is there a upper limit to the number of words in the category?
0
SharathData EngineerCommented:
Do you have any JOIN condition between these two tables? Any common column? If you want to get the records from table A with 'mary' or 'lamb' in any of the columns Description,Action or Accent, why do you want to include table B in the FROM clause if there is no common field.
You can simply do like this. If this is not what you are looking, post some sample data from both tables and the expected result.

SELECT * 
FROM   table_a 
WHERE  (DESCRIPTION LIKE '%Mary%' 
         OR DESCRIPTION LIKE '%lamb%') 
        OR (ACTION LIKE '%Mary%' 
             OR ACTION LIKE '%lamb%') 
        OR (accent LIKE '%Mary%' 
             OR accent LIKE '%lamb%')

Open in new window

0
Glen_DAuthor Commented:
I have about 50 categories in table B - field description.  Let's say the fields are:

blue
green
red
lamb
mary
frank
etc

In table A, I have three text fields that 'may' contain a category in Table B.  For example:

Table A    

Description    Accent    Action
no                   red          slow
mary               green      jog
sam                null          null

My query should return the first two rows of data and if done correctly would also include the field from Table B with the associated value.  For example:

Table A a, Table B b

a.description, a.accent, a.action, b.category
no                   red          slow        red
mary               yellow      jog        mary  ------  same row but different category
mary               green       jog          jog   ------- same row but different category


Thank You
0
Rajkumar GsSoftware EngineerCommented:
Check this script.

This will fetch all the data from TableB and compare each word of the data in TableB with all the 3 fields in TableA (OR)



-- THIS FUNCTION SPLITS STRING
CREATE FUNCTION [dbo].[fnSplit](
    @sInputList VARCHAR(8000) -- List of delimited items
  , @sDelimiter VARCHAR(8000) = ',' -- delimiter that separates items
) RETURNS @List TABLE (item VARCHAR(8000))

BEGIN
DECLARE @sItem VARCHAR(8000)
WHILE CHARINDEX(@sDelimiter,@sInputList,0) <> 0
 BEGIN
 SELECT
  @sItem=RTRIM(LTRIM(SUBSTRING(@sInputList,1,CHARINDEX(@sDelimiter,@sInputList,0)-1))),
  @sInputList=RTRIM(LTRIM(SUBSTRING(@sInputList,CHARINDEX(@sDelimiter,@sInputList,0)+LEN(@sDelimiter),LEN(@sInputList))))
 
 IF LEN(@sItem) > 0
  INSERT INTO @List SELECT @sItem
 END

IF LEN(@sInputList) > 0
 INSERT INTO @List SELECT @sInputList -- Put the last item in
RETURN
END


CREATE TABLE TableA
(
      Description      varchar(256),
      Action            varchar(256),
      Accent            varchar(256)
)

CREATE TABLE TableB
(
      Category      varchar(256)
)

INSERT INTO TableA
SELECT 'Mary had a lamb', 'fast white', 'Curly wool'
UNION
SELECT 'SQL Server 2005', 'T-SQL', 'DB'


INSERT INTO TableB
SELECT 'Lamb belongs to Mary'


-- QUERY STARTS HERE :: SELECT FROM HERE
DECLARE @STRING VARCHAR(MAX)
SELECT  @STRING =
      NULLIF(
      STUFF(
      (SELECT ' ' + Category  
      FROM TableB
      FOR XML PATH('')), 1,1,''),'')


SELECT DISTINCT A.* FROM TableA A CROSS JOIN
      (SELECT * FROM dbo.fnSplit(@STRING, ' ')) B
WHERE CHARINDEX(B.Item, A.Description, 0) > 0 OR
      CHARINDEX(B.Item, A.Action, 0) > 0 OR
      CHARINDEX(B.Item, A.Accent, 0) > 0
-- SELECT TILL HERE TO EXECUTE

Raj
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Glen_DAuthor Commented:
Jog should also be listed as a category in Table B using row example.
0
Rajkumar GsSoftware EngineerCommented:
Does the data, single word or sentence as shown in the main question's example?

I splitted off the words assuming it is a sentence.

Raj
0
Terry WoodsIT GuruCommented:
If the categories are only one word, you can just do this, can't you?

It's only if they are more than one word that you run into trouble.
select a.description, a.accent, a.action, b.category
from table a a, table b b
where a.description like '%'+b.category+'%' or
          a.action like '%'+b.category+'%' or
          a.accent like '%'+b.category+'%'

Open in new window

0
SharathData EngineerCommented:
TerryAtOpus's query shold work for you. Let us know if you still have any issues.
0
Rajkumar GsSoftware EngineerCommented:
In case of sentence, if any word in the data of TableA contains in more than one row in TableB, then how will be the result ?

Raj
0
Glen_DAuthor Commented:
RajkumarGS:,

Yes....in table A, the fields contain text strings hitting 256 bytes so you were correct.  Could you give me an example on how I would call this function in a query using the data I provided?

TerryAtOpus:

The feilds contain strings....I should have been more clear in my example.  What you provided is exactly what I tried to do but was getting too many rows returned, because of the text strings.

Thank You
0
SharathData EngineerCommented:
eventhough the columns in table A contain strings, the query given by TerryAtOpus will give you the actual result. Post the result of that query and your expected result if you are facing issues.
0
Glen_DAuthor Commented:
I can tell you....

One of the actual categories in Table B is Acoustic Testing

One text string in Table A, Description Field - The acoustics in the vacuum chamber is slightly higher in decibel readings than one would allow under normal circumstances.  For that reason, one should not use this facility as a requirement.

Instead of getting that one row above, I'm getting five other rows that do not contain the word acoustic or testing.

Had me puzzled so that's why I asked the question.

Thank You
0
Terry WoodsIT GuruCommented:
Hmm... it's got me puzzled then. Do you get the same 5 other rows if you run:

select a.description, a.accent, a.action, b.category
from table a a, table b b
where a.description like '%Acoustic Testing%' or
          a.action like '%Acoustic Testing%' or
          a.accent like '%Acoustic Testing%'

Open in new window

0
Terry WoodsIT GuruCommented:
Actually, that should be:
select a.description, a.accent, a.action, b.category
from table a a, table b b
where (a.description like '%Acoustic Testing%' or
          a.action like '%Acoustic Testing%' or
          a.accent like '%Acoustic Testing%')
and b.category like '%Acoustic Testing%'

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
>> Could you give me an example on how I would call this function in a query using the data I provided?

This will return on single word and sentence data.

Please check this updated query

Raj
CREATE TABLE TableA
(
      Description      varchar(256),
      Action            varchar(256),
      Accent            varchar(256)
)

CREATE TABLE TableB
(
      Category      varchar(256)
)

INSERT INTO TableA 
SELECT 'Mary had a lamb', 'fast white', 'Curly Wool'
union
SELECT 'no',                   'red',          'slow'
UNION
SELECT 'mary',               'green',      'jog'
UNION
SELECT 'sam',                null,          null


INSERT INTO TableB
SELECT 'Lamb belongs to Mary'
UNION
SELECT 'blue'
union
SELECT 'green'
union
SELECT 'red'
union
SELECT 'lamb'
union
SELECT 'mary'
union
SELECT 'frank'

SELECT * FROM TableA
SELECT * FROM TableB



-- QUERY STARTS HERE :: SELECT FROM HERE
DECLARE @STRING VARCHAR(MAX)
SELECT  @STRING =
      NULLIF(
      STUFF(
      (SELECT ' ' + Category  
      FROM TableB
      FOR XML PATH('')), 1,1,''),'') 


SELECT DISTINCT A.*,
	(SELECT TOP 1 Category FROM TableB WHERE Category LIKE '%' + B.Item + '%')
FROM TableA A CROSS JOIN
      (SELECT * FROM dbo.fnSplit(@STRING, ' ')) B
WHERE CHARINDEX(B.Item, A.Description, 0) > 0 OR
      CHARINDEX(B.Item, A.Action, 0) > 0 OR
      CHARINDEX(B.Item, A.Accent, 0) > 0
-- SELECT TILL HERE TO EXECUTE

Open in new window

0
Rajkumar GsSoftware EngineerCommented:
You need the function [dbo].[fnSplit], that I posted before.

Append 'AS Category' to column from TableB in my query.

Then the select query will be like shown below.

Raj
DECLARE @STRING VARCHAR(MAX) 
SELECT  @STRING = 
      NULLIF( 
      STUFF( 
      (SELECT ' ' + Category   
      FROM TableB 
      FOR XML PATH('')), 1,1,''),'')  
 
 
SELECT DISTINCT A.*, 
        (SELECT TOP 1 Category FROM TableB WHERE Category LIKE '%' + B.Item + '%') AS Category
FROM TableA A CROSS JOIN 
      (SELECT * FROM dbo.fnSplit(@STRING, ' ')) B 
WHERE CHARINDEX(B.Item, A.Description, 0) > 0 OR 
      CHARINDEX(B.Item, A.Action, 0) > 0 OR 
      CHARINDEX(B.Item, A.Accent, 0) > 0

Open in new window

0
Glen_DAuthor Commented:
Thanks Everyone...I'll be working this today and tomorrow and let you know how everything completes.  

Glen
0
Glen_DAuthor Commented:
RajkumarGS,

I really like the elegance of your query.  That said, I am still receiving erroneous data.

Facility                                       Description                           Requirement        Category
Future Flight Central (FFC)             Human-in-the-loop simulations      TBD        Acoustics Testing

The words "Acoustics' or 'Testing' do not appear in this row but the row was returned anyway and appended with the category of Acoustics Testing.

Thoughts?

Thank You
0
lloydswayzeCommented:
I suggest you use more than 1 category field  (category1,category2)

then make your  WHERE statment like

WHERE description like '%'+rtrim(category1)+'%'  
    and (category2 is null  or description like '%'+rtrim(category2)+'%'  
           )

you could have more than 2 category fields just repeat changing to cat..3 etc.
0
lloydswayzeCommented:
if you want a single match to be selected

WHERE description like '%'+rtrim(category1)+'%'  
    OR  (category2 is not null  and description like '%'+rtrim(category2)+'%'  

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.