Posted by Erland Sommarskog on 02/23/06 01:06

Erich93063 (erich93063@gmail.com) writes:
> I have a field for "First Name", a field for "Last Name" and a field
> (looked up by ID) for "Company Name". Now here is the problem. In the
> usual case there will be a company name, a first name and a last name
> for example:
>
> Company Name: ACME Corporation
> First Name: John
> Last Name: Smith
>
> HOWEVER, sometimes the vendor is not part of a company and the users of
> the database have just been entering in the vendors name as the name of
> the company. So it would end up looking like this:
>
> Company Name: John Smith
> First Name: John
> Last Name: Smith
>
> So the problem is that the accounting program that I am importing these
> contacts into uses a weird schema. Instead of separate fields for first
> name, last name and company, they have a combined field for "Company
> Name/Last Name" and then a separate field for first name. So if the
> first name field is blank then we know this contact is a company and
> not an individual. As soon as we provide a first name, the record
> becomes an individual as opposed to a company.
>...
> SELECT CASE WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName + '%' AND
> cu.vchrLastName LIKE '%' + co.vchrCompanyName + '%'
> THEN cu.vchrLastName
> ELSE co.vchrCompanyName
> END AS [Co./Last Name],
> CASE WHEN cu.vchrFirstName LIKE '%' + co.vchrCompanyName + '%' AND
> cu.vchrLastName LIKE '%' + co.vchrCompanyName + '%' THEN
> cu.vchrFirstName
> ELSE ''
> END AS [First Name],

You have it backwards: You are trying to see if "John" is like
%John Smith% which it isn't. But "John Smith" is like "%John%".

Really how you should do this, I don't know, because I suspect that
the quality of the data is not perfect. It it was, you should
just say:

CASE WHEN FirstName + ' ' + LastName = CompanyName

But users may have entered extra spaces between the first and last
name - or no space at all.

You could try

CASE WHEN CompanyName LIKE '%' + FirstName '%' AND
CompanyName LIKE '%' + LastName '%'

But there is entry where the users entered X first and last name, and
the company has an X in the name...

It would have been a lot simpler, if you had permitted CompanyName
to be NULL for private vendros.

By the way, in the output, shouldn't {First Name] be NULL rather than
'' for companies?

--
Erland Sommarskog, SQL Server MVP, esquel@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

[Back to original message]