identify zipcode - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

identify zipcode

Author

Topic

mike13
Posting Yak Master

219 Posts

Posted - 2014-05-15 : 11:51:37

Hi all,

I got city and zipcode switch on big amount of customer records (programmer made error inserting records).

i want to correct it, but how can do

where zipcode like (if it has numbers in it)

do not think there is any city in the world that has numbers in the name ;-)

thanks a lot

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2014-05-15 : 11:59:34

Is the city all numbers? if so, then you can try a WHERE clause like:
WHERE col1 NOT LIKE '%[^0-9]%'

mike13
Posting Yak Master

219 Posts

Posted - 2014-05-15 : 12:35:21

no sorry forgot some zipcode have letters.
Here is sample of some whole batch that are wrong.
Maybe it is easier if if just chars including country specific (ex: glyngøre)

zipcode, city, country

glyngøre 7870 DENMARK
São Paulo 2264070 BRAZIL
hull hu95ty UNITED KINGDOM
carrizal 1201 VENEZUELA
forest hill 76140 UNITED STATES
carolina 982 PUERTO RICO
Monroe 97456 UNITED STATES
glasgow g349fb UNITED KINGDOM
Timisoara 300510 ROMANIA
leeds ls133rg UNITED KINGDOM
brighton East sussex bn25fj UNITED KINGDOM
Niagara Falls On L2E 3T1 CANADA
Makawao 96768 UNITED STATES
london nw10 8sa UNITED KINGDOM
Sofia 1000 BULGARIA
dunedin 9012 NEW ZEALAND
oakland 94606 UNITED STATES
bramming 6740 DENMARK
stevenson ranch 91381 UNITED STATES

mike13
Posting Yak Master

219 Posts

Posted - 2014-05-19 : 05:58:38

I figured it out

(zipcode LIKE '%[a-z]%'
OR zipcode LIKE '%[A-Z]%') aNd not( zipcode like '%[0-9]%')

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-05-19 : 19:09:22

If no City has a digit in its name...[CODE]update MyTable
set City = Zipcode,
Zipcode = City
where City like '%[0-9]%'[/CODE]Also, I'm wondering if you're getting yourself in trouble with Unicode characters, like in "São Paulo" which may lie outside the [a-z] spectrum.

Opportunity is missed by most people because it is dressed in overalls and looks like work. - T.A.Edison

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources