Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Wild Card search for characters

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

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

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 49

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 49

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 49

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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Loops Section Overview
Suggested Courses

926 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