Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Where Like clause assist 1

Status
Not open for further replies.

Dashley

Programmer
Dec 5, 2002
925
US
Hi,

Using sqlExpress
I have two tables tblcompany and tblpersonnel. tblcompany.companyid = tblpersonnel.companyid

tblcompany
companyname
companyid
email

tblpersonnel
companyid
firstname
lastname


tblcompany has a company called Booksellers, companyid = 5 and a email address of bWhite@booksellers.com

tblpersonnel has 3 individuals assigned to companyid 5, Sherry Walker, Jim Beam and Barry White.

I can join the tables and pull the company and its corresponding personnel. But..
I only want the person whose lastname matches (like) the lastname in the email address.

Thanks!



 
You'll get weird results if there are multiple employees with the same last name.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Thanks IMEX.

Hi and Yeah George I did. I don't who collected this data but they weren't to bright. The stored the emails against the company name rather than the individuals. I'm just trying ways to clean it up so It can be used for some mailings.


 
If the email addresses are always in the form: <first initial><last name>@<whatever>, you can make it more precise by using:

like '%' + substr(p.firstname, 1, 1) + p.lastname + '@%'

I'd include the '@' regardless to avoid issues with one name contained in another (Smith, Smithson).

Since most probably won't have more than one with the same last name, you could check the count, make the change automatically where you only get one match, keep a record of the others, and then handle them manually.

Tamar
 
Thanks Tamar I'll give it a try.

@BigRed 75k + rows in the tblcompany and 286k + in the personnel table. Nothing I'd want to manually parse through HA.
 
@Tamar Opps didn't see your post until today. Thanks for the '@%' tip. I'll put it to good use,
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top