Counting the number of satisfied LIKES in a SELECT statement

Counting the number of satisfied LIKES in a SELECT statement

Post by Kurt Mila » Wed, 01 Sep 1999 04:00:00



I wish to count the number of times a record meets LIKE requirements in a
SELECT statement, i.e.

SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
TableExample.Name LIKE '%TheOther%'

So that a record in TableExample where Name= "This and That" would be
returned by the above SQL as:

Name                            Matches
This and That               2

Can I do this in a single SQL statement?

Thanks,
Kurt

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by BPMargoli » Wed, 01 Sep 1999 04:00:00


Kurt,

select Name, sum (Matches) as Matches

FROM (SELECT Name, Count(Something) AS Matches FROM TableExample
             WHERE TableExample.Name LIKE '%this%'
             GROUP BY Name

             UNION ALL

            SELECT Name, Count(Something) AS Matches FROM TableExample
            WHERE  TableExample.Name LIKE '%that%'
            GROUP BY Name

            UNION ALL

            SELECT Name, Count(Something) AS Matches FROM TableExample
            WHERE TableExample.Name LIKE '%TheOther%'
            GROUP BY Name ) as UnionTable

It's a single SQL statement, but it is three passes through the table.


Quote:> I wish to count the number of times a record meets LIKE requirements in a
> SELECT statement, i.e.

> SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
> TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
> TableExample.Name LIKE '%TheOther%'

> So that a record in TableExample where Name= "This and That" would be
> returned by the above SQL as:

> Name                            Matches
> This and That               2

> Can I do this in a single SQL statement?

> Thanks,
> Kurt


 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by Charles Bretan » Wed, 01 Sep 1999 04:00:00


Kurt,

    If I understand you correctly, then you want every record in the table,
and for each record, the "Name" column , and the count of how many of a set
of string values exist in that "Name" column value...

Select Name,
    Case When Name Like '%This%' Then 1 Else 0 End +
    Case When Name Like '%That%' Then 1 Else 0 End +
    Case When Name Like '%The Other%' Then 1 Else 0 End
    As Matches
From TableExample

Charly


Quote:> I wish to count the number of times a record meets LIKE requirements in a
> SELECT statement, i.e.

> SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
> TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
> TableExample.Name LIKE '%TheOther%'

> So that a record in TableExample where Name= "This and That" would be
> returned by the above SQL as:

> Name                            Matches
> This and That               2

> Can I do this in a single SQL statement?

> Thanks,
> Kurt

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by Steve Jorgense » Wed, 01 Sep 1999 04:00:00


Sure you can:

SELECT Name, CASE WHEN TableExample.Name LIKE '%this%' THEN 1 ESLE 0 END +
CASE WHEN TableExample.Name LIKE '%that%' THEN 1 ESLE 0 END + CASE WHEN
TableExample.Name LIKE '%TheOther%' THEN 1 ESLE 0 END AS Matches FROM
TableExample WHERE
TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
TableExample.Name LIKE '%TheOther%'


>I wish to count the number of times a record meets LIKE requirements in a
>SELECT statement, i.e.

>SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
>TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
>TableExample.Name LIKE '%TheOther%'

>So that a record in TableExample where Name= "This and That" would be
>returned by the above SQL as:

>Name                            Matches
>This and That               2

>Can I do this in a single SQL statement?

>Thanks,
>Kurt

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by Kurt Mila » Wed, 01 Sep 1999 04:00:00


Absolutely marvellous.

I haven't had much of an occasion to work with UNION queries in the past.

Thank you so much. (and thank for the help on my decimal question - I had it
set up as you suggested, just wanted to make sure I understood correctly).

Another question, if you don't mind. Is there a way to perform a backwards
LIKE comparison (for lack of a better term).

Let's say I have the following 3 tables

tblDocs
DocID (PK)
Doc (Text)

tblRequest
ReqeustID (PK)

tblReqKW
RequestID (FK to tblRequest.RequestID)
Keyword (varchar)

I'd like to say:

SELECT tblReqKW.FID
FROM tblReqKW
WHERE tblDocs Like '%tblReqKW.Keyword%'
(all of the above where tblDoc.DocID=SomeNumber)

Obviously the above syntax won't work, but I'd like to take that general
idea and expand on it with your UNION example so that I can compare new
Requests to existing Docs (as the UNION example you gave does) and compare
new Docs to open Requests.

I've gotten this to work via different means in the past (used a tblDocKW
and an INNER JOIN query), but am trying to change the operation now. What I
wish to end up with is a sort of Matching score so that I can put Doc.DocID,
Request.ID and Score into a table joining Docs to Requests.

Anyway, I really appreciate your time and your exactly on the mark example.

Kurt

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by BPMargoli » Wed, 01 Sep 1999 04:00:00


Charles,

Better than my solution, but I think you meant:

Select Name,
    SUM (Case When Name Like '%This%' Then 1 Else 0 End +
               Case When Name Like '%That%' Then 1 Else 0 End +
               Case When Name Like '%The Other%' Then 1 Else 0 End)
    As Matches
From TableExample
Group By Name


> Kurt,

>     If I understand you correctly, then you want every record in the
table,
> and for each record, the "Name" column , and the count of how many of a
set
> of string values exist in that "Name" column value...

> Select Name,
>     Case When Name Like '%This%' Then 1 Else 0 End +
>     Case When Name Like '%That%' Then 1 Else 0 End +
>     Case When Name Like '%The Other%' Then 1 Else 0 End
>     As Matches
> From TableExample

> Charly



> > I wish to count the number of times a record meets LIKE requirements in
a
> > SELECT statement, i.e.

> > SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
> > TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
> > TableExample.Name LIKE '%TheOther%'

> > So that a record in TableExample where Name= "This and That" would be
> > returned by the above SQL as:

> > Name                            Matches
> > This and That               2

> > Can I do this in a single SQL statement?

> > Thanks,
> > Kurt

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by Kurt Mila » Wed, 01 Sep 1999 04:00:00


Charles and BP,

Charles - right on the money. I think I managed a similar query similar to
the method you exemplified here 9but it's been a long, long time).

BP - thanks again.

Kurt


Quote:> Charles,

> Better than my solution, but I think you meant:

> Select Name,
>     SUM (Case When Name Like '%This%' Then 1 Else 0 End +
>                Case When Name Like '%That%' Then 1 Else 0 End +
>                Case When Name Like '%The Other%' Then 1 Else 0 End)
>     As Matches
> From TableExample
> Group By Name

 
 
 

Counting the number of satisfied LIKES in a SELECT statement

Post by Charles Bretana Jr » Thu, 02 Sep 1999 04:00:00


BP,

    I think he wanted the number of matches for each row in the source
table.........

 Charly


> Charles,

> Better than my solution, but I think you meant:

> Select Name,
>     SUM (Case When Name Like '%This%' Then 1 Else 0 End +
>                Case When Name Like '%That%' Then 1 Else 0 End +
>                Case When Name Like '%The Other%' Then 1 Else 0 End)
>     As Matches
> From TableExample
> Group By Name



> > Kurt,

> >     If I understand you correctly, then you want every record in the
> table,
> > and for each record, the "Name" column , and the count of how many of a
> set
> > of string values exist in that "Name" column value...

> > Select Name,
> >     Case When Name Like '%This%' Then 1 Else 0 End +
> >     Case When Name Like '%That%' Then 1 Else 0 End +
> >     Case When Name Like '%The Other%' Then 1 Else 0 End
> >     As Matches
> > From TableExample

> > Charly



> > > I wish to count the number of times a record meets LIKE requirements
in
> a
> > > SELECT statement, i.e.

> > > SELECT Name, Count(Something) AS Matches FROM TableExample WHERE
> > > TableExample.Name LIKE '%this%' OR TableExample.Name LIKE '%that%' OR
> > > TableExample.Name LIKE '%TheOther%'

> > > So that a record in TableExample where Name= "This and That" would be
> > > returned by the above SQL as:

> > > Name                            Matches
> > > This and That               2

> > > Can I do this in a single SQL statement?

> > > Thanks,
> > > Kurt