Solved

Wild Card search for characters

Posted on 2013-01-18
10
418 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

 

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

How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Rewrite without the NULLIF 4 33
Union & Crosstab qrys 101! 6 73
Can a Trigger trigger a Trigger? 4 47
Output Different in Excel Compared In SSMS 8 14
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
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…

739 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