?
Solved

How to use like to search a text field

Posted on 2010-03-23
28
Medium Priority
?
433 Views
Last Modified: 2012-05-09
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


0
Comment
Question by:Glen_D
  • 9
  • 8
  • 5
  • +2
27 Comments
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28377821
Are you sure you're joining correctly? I would have thought like '%Mary%' should work just fine.
0
 

Author Comment

by:Glen_D
ID: 28378328
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28378576
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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
LVL 35

Expert Comment

by:Terry Woods
ID: 28378642
You need to join the tables to get a sensible result, probably.
0
 

Author Comment

by:Glen_D
ID: 28378746
There is nothing to join on....

Join on a 'Like' statement?
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28379181
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
 

Author Comment

by:Glen_D
ID: 28381126
yes...a partial match is exactly what I'm looking for

Thank You
0
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28381270
Is there a upper limit to the number of words in the category?
0
 
LVL 41

Expert Comment

by:Sharath
ID: 28383338
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
 

Author Comment

by:Glen_D
ID: 28383804
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
 
LVL 23

Accepted Solution

by:
Rajkumar Gs earned 2000 total points
ID: 28383826
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
 

Author Comment

by:Glen_D
ID: 28383867
Jog should also be listed as a category in Table B using row example.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28384544
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28384587
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
 
LVL 41

Expert Comment

by:Sharath
ID: 28384857
TerryAtOpus's query shold work for you. Let us know if you still have any issues.
0
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28385120
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
 

Author Comment

by:Glen_D
ID: 28385126
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
 
LVL 41

Expert Comment

by:Sharath
ID: 28385807
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
 

Author Comment

by:Glen_D
ID: 28386424
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28387138
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
 
LVL 35

Expert Comment

by:Terry Woods
ID: 28411997
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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28412616
>> 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
 
LVL 23

Expert Comment

by:Rajkumar Gs
ID: 28412984
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
 

Author Comment

by:Glen_D
ID: 28425282
Thanks Everyone...I'll be working this today and tomorrow and let you know how everything completes.  

Glen
0
 

Author Comment

by:Glen_D
ID: 28579208
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
 
LVL 5

Expert Comment

by:lloydswayze
ID: 32349033
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
 
LVL 5

Expert Comment

by:lloydswayze
ID: 32349203
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question