[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

query for finding a string

Hi,

When I need to qrite a query for which the value in the table is characters, I have to put them in quotes. In my situation because of a poor database design, there is field, called "country of education' and the has values like france - xxx university, USA-sraucse Uni. BAsically the field contains country and the name of university as well.

Now I need to search for people who have not studied from USA e.g. but I cannot say

........ WHERE Countrt_of_Education <> 'USA' because there are very few USA only. So I need to say <>'Any thing which contains USA'

Can anyone please guide me on how to do that?

Thanks and Regards
0
naufal
Asked:
naufal
  • 3
  • 2
1 Solution
 
JimBrandleyCommented:
The predicate you need is:
WHERE country of education LIKE '%USA%'
0
 
naufalAuthor Commented:
Not working for me, I wrote something like this

SELECT [Personal Data].ID, [Personal Data].[First Name], [Personal Data].[Last Name], Education.Country_Of_Eduction
FROM [Personal Data] INNER JOIN Education ON [Personal Data].ID = Education.ID
WHERE Education.Country_Of_Eduction LIKE '%Canada%';

Returns nothing... Actually I have to write a query for NOT Like, i mean WHERE Education.Country_Of_Eduction <> 'Canada';
0
 
naufalAuthor Commented:
Actually one more thing, I am not sure of, the values typed in are something like

France - University of Paris meaning there are even spaces in between, but i guess % should have counted those, no?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
naufalAuthor Commented:
Got it thanks your LIKE predicate helped, got the NOT LIKE
0
 
harfangCommented:
In Access, the wildcard character is '*', not '%'.

Also, judging from your query, "people who have not studied from USA" is not precise enough. You need to choose between

"people who have never studied in the USA (but perhaps elsewhere)"

SELECT * FROM [Personal Data]
WHERE ID Not In (
    Select ID From Education
    Where Country_Of_Eduction Like 'USA*'
    )

"people who have studied outside of the USA (but perhaps also in the USA)"

SELECT * FROM [Personal Data]
WHERE ID In (
    Select ID From Education
    Where Country_Of_Eduction Not Like 'USA*'
    )

"people who have studied, but never in the USA"

SELECT * FROM [Personal Data]
WHERE ID In (
    Select ID From Education
    )
AND ID Not In (
    Select ID From Education
    Where Country_Of_Eduction Like 'USA*'
    )

The last one assumes that a record in Education means "have studied".

Cheers!
(°v°)
0
 
harfangCommented:
Hmm. A bit slow... -- (^v°)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now