Solved

Wild Card search for characters

Posted on 2013-01-18
10
417 Views
Last Modified: 2013-01-18
Hi Experts,
I have a question about using the wild card in a query.  I have a address table and there is a zip code field.  For the address that is in U.S, off course that we have the us zip code such as 60610, 36507, 36037 etc. but we make up the zip code for out of country. for example, if the person live in Poland, we have the address, but in the zip code column, we enter as PO001, or PO002 such.  now my question is how can I use the wild card in the query the just pull the address the is out of country such PO001 or PO002.  I tried to use like "??*", but it doesn't work.  I know the ? is for cahracters. Can anyone have any idea?
Thanks
0
Comment
Question by:jodstrr2
  • 5
  • 4
10 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 38793663
If you want to *search* for Poland addresses, it would be:

SELECT * FROM YourTable WHERE Zip LIKE "PO*"

However if you want to extract the country code:

SELECT *, LEFT(Zip,2) AS CountryCode FROM YourTable WHERE Zip LIKE "??*"
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38793688
Or, you could try:

WHERE [Zip] NOT LIKE "[0-9][0-9][0-9][0-9][0-9]"

Actually, zips don't go all the way up to "99999"

or maybe something like:

WHERE [Zip] Like "[A-Z][A-Z]*"
0
 

Author Comment

by:jodstrr2
ID: 38793745
beside POland, we still have other countries.  I use WHERE [Zip] Like "[A-Z][A-Z]*" seems works
0
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 

Author Comment

by:jodstrr2
ID: 38793767
sorry fyed, I did not finish typing and click submit.  The WHERE [Zip] Like "[A-Z][A-Z]*" seems works for if just POland, but is doesn't work if I want to pull the max number, for example, I have PO001, PO002, PO003, GE001, Ge002, KO001, it only show PO003, the GE002 and KO001 did not show.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38793792
Are you trying to get all of the ZipCodes that start with two alpha characters?

Can you post your SQL?  It looks like you must be doing a Max([Zip]) and a GroupBy Left([ZIP], 2).
0
 

Author Comment

by:jodstrr2
ID: 38793826
you are correct, I'm trying to get all the zip codes that start with two alpha character and I tried to Max the Zip, was it something I did wrong in this case?
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 38793871
"I tried to Max the Zip, was it something I did wrong in this case?"

Yes.  If you want all of the records where the zip is outside the country, it would be something like:

SELECT * FROM yourTable WHERE [Zip] Like "[A-Z][A-Z]*"

You can add an ORDER BY clause if you want, but you should get rid of any GROUP BY clause.

SELECT * FROM yourTable WHERE [Zip] Like "[A-Z][A-Z]*"
ORDER BY [Zip], [Street]
0
 

Author Comment

by:jodstrr2
ID: 38794579
I need to show all the records where the zip is outside of the country, I just want to show the max of the zip code.
for example, if some one entered PO003 for an address for Poland then I can show the last zip code# for outside of the country for Poland was PO003, so that next user knows she can use PO004 not PO003. same as for other countries This query is for DLoopup only.
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 38794748
Then you could do something like:

SELECT Left([Zip],2), MAX([Zip]) as MaxUsed
From yourTable
WHERE ZIP Like "[A-Z][A-Z]*"
GROUP BY Left([Zip],2)

This would give you:
GE     GE009
PO    PO003

etc
0
 

Author Closing Comment

by:jodstrr2
ID: 38794860
Thanks, it works the way that I want.
0

Featured Post

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
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…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

763 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