Format zip code - string mask?

[jenlion]

Hi all,

I have a table with a field for "postal code" and a field for "country".

I have another table that stores the "country" and the "postal code mask", which is the country's appropriate format.

For example, in the format table, I have:
Country PostalCodeMask
----------------------------
USA 99999-9999
CAN N9N 9N9

My table holding the data looks like this:
Country PostalCode
----------------------------
USA 164715324
CAN L9B3C4

(these are made up postal codes, but the real data in the table has already been verified and is correct).

I don't need to verify numbers/letters; I just need to insert dashes or spaces in the correct spots depending on the format.

I need to do this in an efficient way, too. Processing thousands of these at a time for reports generated several times a day. I'm looking to use something like 'update datatable set postalcode = (postal code using mask from other table)'

I can update this field without consequences because this is a temporary holding table used for printing a crystal report only.

I need the results to be:

Country PostalCode
----------------------------
USA 16471-5324
CAN L9B 3C4

Can anyone help with a good function for this? Pretty urgent for me.

Thanks!

 

[Catadmin]

Try this. It's not a function, but it should work.

Update TableA
Set PostalCode = Select Substring(PostalCode, 1, 5) + '-' + Substring(PostalCode, 6, 9)
where Country = 'USA'

Update TableA
Set PostalCode = Select Substring(PostalCode, 1, 3) + '-' + Substring(PostalCode, 4, 6)
where Country = 'CAN'

I'm assuming that the PostalCode column is already set to a Char value of some sort instead of an Int.



Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jenlion]

Thanks Cat,

I would happily do this -- but I used USA and CAN as a familiar example.

Unfortunately, there are actually 239 countries involved here. *Most* of them use "99999" as the format -- but I need to give the opportunity for them to update that or add better ones in the future.

 

[Catadmin]

Hmm, this is going to be a hard one, then, because you have to account in your function or stored procedure for each and every different country's formatting issue.

I would create an SP that accepts COUNTRY, FirstPartLastNum and SecondPartFirstNum as input parameters.

This is an untested example:

Create Procedure ZipFormat (@Country char(3), @1PtLastNum int, @2PtFirstNum int)
AS

Update TableA
Set PostalCode = Select Substring(PostalCode, 1, @1PtLastNum) + '-' + Substring(PostalCode, @2PtFirstNum, Len(PostalCode))
where Country = @Country

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[evaleah]

I would generally do the same thing as Catadmin has recommended but I would do this in a function that returns the format you want.

So I would do something like this:

CREATE FUNCTION fnPostalCode (@country nvarchar(10), @code nvarchar(10))
RETURNS nvarchar(20)
AS
BEGIN
  DECLARE @PostalCode nvarchar(20)

  SET @PostalCode = CASE @country
       WHEN 'USA' THEN --whatever your format would be
       WHEN 'CAN' THEN ---whatever format
      -- then add more when clauses for the other different formats.
      -- Since you said that most formats are "99999" I would make that my else statement.
       ELSE @code END

   RETURN @PostalCode
END

After creating the function then you can just call it where you need it. You can either update your temporary table or just call the function from the sproc that populates your table.

HTH
Eva

 

[jenlion]

Can't do a case for each country because there are so many. Might be able to do a case on the distinct format, though, find out what space isn't a number or letter and insert that space approriately into everything that shares that country... but that requires a cursor. Ugh!

I think I have a different solution, I'm going to use the "picture" function in Crystal and hope it doesn't slow things down too much.

Thanks for the suggestions!

 

[SQLBill]

Since it's pretty rare for a country to change it's postal codes....I wouldn't want a script that 'runs' frequently and updates everything. Instead, I would correct everything now (yes it might take a while, but that's what happens when data is entered incorrectly). Then create a trigger that will only allow the postal codes to be entered in the proper format (either fails on incorrect codes or corrects them and then enters them). This means you would not have to keep running a 'validation' script on the table and if something did change, you would only run a script against one country's values.

-SQLBill

Posting advice: FAQ481-4875

 

[jenlion]

Actually Bill, the items are not entered incorrectly. This table is part of a larger system that requires the postal codes to be entered with no formatting; when you set up a country, you give it the format once. So, for telephone numbers and zip codes, you *never* see incorrect formats as long as you are using their code to display them!

I'm not using their code, though. The function they are using for this causes requires other calls to their software that takes too long to run -- I need to process this faster.

Thanks anyway though!! I have been through cleanups and sometimes they are required.