Select records based on what a field begins with?

Question

I have a database with a table that has identifiers c1, c2, c3..etc..

Instead of writing a query that has a bunch of ORs in it, how can I modify the below query with something that will catch all the records that begin with a certain letter?

SELECT 
    Person.spineinjuryAdmit, 
    tblComorbidity.comorbidityexplanation,
    Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation

FROM tblKentuckyCounties 
INNER JOIN (tblComorbidity 
            INNER JOIN (Person 
                        INNER JOIN tblComorbidityPerson 
                            ON Person.PersonID = tblComorbidityPerson.personID) 
                ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) 
    ON tblKentuckyCounties.ID = Person.County
GROUP BY    Person.spineinjuryAdmit, 
            tblComorbidity.comorbidityexplanation
HAVING (((Person.spineinjuryAdmit)="c1" Or 
         (Person.spineinjuryAdmit)="c2" Or 
         (Person.spineinjuryAdmit)="c3"));

I'm not sure if you prefer doing it that way, but I like to construct my join statements like this: `FROM tblKentuckyCounties k` `JOIN Person p ON p.County = k.ID` `JOIN tblComorbidityPerson cp ON cp.personID = p.PersonID` `JOIN tblComorbidity c ON c.ID = cp.comorbidityFK` — Calvin Fisher, May 31 '11 at 20:04

score 30 · Answer 1 · answered May 31 '11 at 19:28

30

Have you tried using LIKE? As an example:

SELECT * FROM patients WHERE lastName LIKE 'm%';

This would return records where patients.lastName starts with 'm'. The '%' character may be '*' for access, I can't remember. In some databases, you can also use '_' which will match a single character (or however many underscores you add).

answered May 31 '11 at 19:28

Rob

7,377
7
36
38

i tried to use the wild card % but i did it wrong initially so i came to SO for some help. – dzilla May 31 '11 at 19:33
2

The traditional Access (Jet/ACE) wildcards are * and ?, using the default "ANSI 89 SQL Mode" (which is proprietary to Access). % and _ are ANSI 92 SQL, and you can run Access in ANSI 92 SQL mode, but I wouldn't recommend it (it breaks many things in an application built in ANSI 89 mode). Alternatively, if you want to use the ANSI 92 wildcards, you can use the proprietary ALIKE operator, or run your SQL with OLEDB/ADO, which uses ANSI 92 mode by default (DAO use ANSI 89). – David-W-Fenton Jun 03 '11 at 03:30

score 4 · Accepted Answer · answered May 31 '11 at 19:32

    SELECT Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation, Count(tblComorbidity.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties INNER JOIN (tblComorbidity INNER JOIN (Person INNER JOIN tblComorbidityPerson ON Person.PersonID = tblComorbidityPerson.personID) ON tblComorbidity.ID = tblComorbidityPerson.comorbidityFK) ON tblKentuckyCounties.ID = Person.County
GROUP BY Person.spineinjuryAdmit, tblComorbidity.comorbidityexplanation
HAVING (Person.spineinjuryAdmit LIKE "c*");

will accept as soon as i'm allowed. i figured asking SO would be quicker than googling. thanks :) — dzilla, May 31 '11 at 19:36

score 4 · Answer 3 · answered May 31 '11 at 19:57

You can use a WHERE clause to exclude the rows you don't want before doing the GROUP BY.

SELECT 
    p.spineinjuryAdmit, 
    c.comorbidityexplanation,
    Count(c.comorbidityexplanation) AS CountOfcomorbidityexplanation
FROM tblKentuckyCounties AS k
INNER JOIN (tblComorbidity AS c
            INNER JOIN (Person AS p
                        INNER JOIN tblComorbidityPerson AS cp
                            ON p.PersonID = cp.personID) 
                ON c.ID = cp.comorbidityFK) 
    ON k.ID = p.County
WHERE p.spineinjuryAdmit ALike "c%"
GROUP BY    p.spineinjuryAdmit, 
            c.comorbidityexplanation

If your query is executed in SQL-89 mode, you can use this as your WHERE clause.

WHERE p.spineinjuryAdmit Like "c*"

In SQL-92 mode, you need the standard ANSI wild card.

WHERE p.spineinjuryAdmit Like "c%"

I used ALike to tell the database engine to expect ANSI wild cards.

SQL-89 mode is used by DAO ... unless you've set the database option to use SQL-92 mode ("SQL Server compatible syntax).

If you're running a query with ADO, it will always use SQL-92 mode.

score 1 · Answer 4 · answered Jul 03 '17 at 21:58

1

You can use regexp to query all rows that starts with several characters.

SELECT * FROM table WHERE column REGEXP '^[ c1, c2, c3]';

This query will return all rows where column starts with 'c1' or 'c2' or 'c3'.

answered Jul 03 '17 at 21:58

thodoris

38
3

score 1 · Answer 5 · edited Jul 03 '17 at 22:03

1

You have two options:

Use the LIKE operator
Use the IN operator

For example:

Person.spineinjuryAdmit LIKE "c*"

Person.spineinjuryAdmit IN ("c1", "c2", "c3")

See http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx for details about LIKE.

Fair warning: The wildcards of LIKE in Access are * and ? instead of % and _ (as is the case for most other versions of SQL).

edited Jul 03 '17 at 22:03

Sᴀᴍ Onᴇᴌᴀ

8,218
8
36
58

answered May 31 '11 at 19:39

rskar

4,607
25
21

score 0 · Answer 6 · answered May 31 '11 at 19:35

You could modify this to include the filter list in the WHERE clause. The following will find patients whose last name starts with Smith. (i.e. Smith and Smithson, etc), and those whose Admit start with c.

....
WHERE spineinjuryAdmit LIKE 'c*'
AND   Patient.FirstName LIKE 'Smith*'
GROUP BY    Person.spineinjuryAdmit,             
            tblComorbidity.comorbidityexplanation;

Select records based on what a field begins with?

6 Answers6