asked on

SQL Query

I have a table with two fields Country and Zip. say like
country-zip
US 60074
US 99001-9900
us 99000
IN 600741
IN 834938
CA 1A2 C3G
CA 9K0 I9Y
PK 999
GB 9999999

I want the result to be something like this :
country-zip
US 99001-9900
us 99000
IN 600741
CA 1A2 C3G
PK 999
GB 9999999

It can choose any zip for one country if there are two.
Please help

ralmada

select country, max(zip)
from yourtable
group by country

Bhavesh Shah

hi,

it seems your search is case-sensitive.

i found this.

http://blog.sqlauthority.com/2007/04/30/case-sensitive-sql-query-search/

it might helps

select country, max(zip) zip
from yourtable COLLATE Latin1_General_CS_AS = 'casesearch'
group by country

Open in new window

himabindu_nvn

ASKER

I need to retrive the data based on the zipcode format like if for US there the 2 different formats like 60074 and 60074-9430 In this case i need to retrive both the records.

ralmada

select country, format, max(zip) as zip
from (
select country, case when len(zip) > len(replace(zip, '-', '')) then 1 else 0 end as format, zip
from yourtable
) a
group by country, format

ralmada

or like this

select country, format, max(zip) as zip
from (
select country, case when charindex('-', zip) > 0 then 1 else 0 end as format, zip
from yourtable
) a
group by country, format

Lowfatspread

how do you define a different format?

you either need a "format type" column or some simple test that we can perform...

dannocracker

You'll want to use regular expression type search. See this site for more info. http://msdn.microsoft.com/en-us/magazine/cc163473.aspx. I find it easier to develop regex code in c# and deploy as CLR.

ASKER CERTIFIED SOLUTION

lludden

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

himabindu_nvn

ASKER

helped me to some extent