> Hi
>
> I have a table containing address of contacts.  I have been asked to extract
> these details, but there must be a separation between Address and Postcode.
>
> The Address fields are made up of 6 fields.
>
> The script below is for the table described above:
>
> CREATE TABLE [Customer Address Unit Details] (
>  [First Name] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Surname] [char] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Address Line 1] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Address Line 2] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Address Line 3] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Address Line 4] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Address Line 5] [char] (42) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Post Code] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Telephone Number] [char] (25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
>  [Email ID] [char] (25)
> ) ON [PRIMARY]
> GO
>
> I can quite eaily concatonate the Address fields 1-5, by delimiting the
> string, and then query the Postcode as another field.  The problem that I
> have, is that sometimes the postcode has been entered on to the Address
> fields 1-5 instead of the designated Postcode field.
>
> What is the easiest that I can extract the Postcode for all Customers, for
> when the Postcode does not exist in the designated field?
>
> Kind Regards
>
> Ricky
> (SQL2K/Win2K)

"Erland Sommarskog" <esq***@sommarskog.se> wrote in message
news:Xns9835B9347BBYazorman@127.0.0.1...
> ricky (ri***@ricky.com) writes:
> > I can quite eaily concatonate the Address fields 1-5, by delimiting the
> > string, and then query the Postcode as another field.  The problem that
I
> > have, is that sometimes the postcode has been entered on to the Address
> > fields 1-5 instead of the designated Postcode field.
> >
> > What is the easiest that I can extract the Postcode for all Customers,
for
> > when the Postcode does not exist in the designated field?
>
> [Post Code] = CASE WHEN coalesce([Post Code], '') LIKE @pattern
>                    WHEN coalesce([Address Line 5), ''] LIKE @pattern
>                    ...
>               END
>
> @pattern here has been assigned a pattern that describes how post codes
> looks where you are located. (Which judging from your IP address is the
> UK, and I am definitely not going to try to suggest a pattern for thos
> post codes!)
>
> Since post codes may have entered with or without spaces, you may also
> want to add things like:
>
>    coalesce(replace([Post Code], ' ', ''), '')
>
> Yes, this will be messy.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se
>
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

"--CELKO--" <jcelko***@earthlink.net> wrote in message
news:1157499952.842783.7440@p79g2000cwp.googlegroups.com...
> >>  I am from the UK. <<
>
> I do know if this is still true for 'UK postcode databases', but the
> Royal Mail holds the copyright and will licence you an 'outwards'
> database (i.e. the first segment of the full postcode) for about 150 a
> year, or an 'inwards' database (i.e. the full 9-or 10-character
> postcode) for about 500 per year.
>
> Throw the Postal Codes in a table and use a REFERENCES constraint.
> Didn't you use a column (which is nothing like a field!!) which as too
> big?
>
> Your approach is to invite as much bad data as you can, then clean it
> up as an after thought.  If you were going to stage data then use a
> file (which does have fields), then bring it in.  Your front end
> language should support files and scrubbing.
>
> Dassgar Systems
>  Box #915
>  Bennington, VT 05201-0915
>  USA
>  Global-Z; international postal codes for lots of places
>

> This "CASE" statement lacks "THEN" expressions. You probably meant
> something like that:
>
> [Post Code] = CASE
>   WHEN coalesce([Post Code],      '') LIKE @pattern THEN [Post Code]
>   WHEN coalesce([Address Line 5], '') LIKE @pattern THEN [Address Line 5]
>   WHEN coalesce([Address Line 4], '') LIKE @pattern THEN [Address Line 4]
>   WHEN coalesce([Address Line 3], '') LIKE @pattern THEN [Address Line 3]
>   WHEN coalesce([Address Line 2], '') LIKE @pattern THEN [Address Line 2]
>   WHEN coalesce([Address Line 1], '') LIKE @pattern THEN [Address Line 1]
> END
>
> Probably there should be some extraction done in those "THEN" expressions
> (because "Address Line 5" can contain post code plus some other address
> data).