Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Wild Card search for characters

Posted on 2013-01-18
10
Medium Priority
?
420 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 48

Expert Comment

by:Dale Fye
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
More Than Just A Video Library

Train for your certification. Learn the latest DevOps tools. Grow your skillset to do better work.

At Linux Academy, we release new training modules every week so you'll always be up to date on the latest tech.

 

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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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 48

Accepted Solution

by:
Dale Fye earned 2000 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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

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

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…
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

722 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