Solved

Wild Card search for characters

Posted on 2013-01-18
10
413 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
 

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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

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 your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now