Where Like clause assist 1

[Dashley]

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!

 

[imex]

Hi,

Try something like this:

select * 
from tblcompany as c
inner join tblpersonnel as p
    on p.companyid = c.companyid
where
    c.email like '%' + p.lastname + '%'

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[gmmastros]

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

 

[Dashley]

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.

 

[BigRed1212]

How many records are you talking?

 

[TamarGranor]

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

 

[Dashley]

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.

 

[Dashley]

@Tamar Opps didn't see your post until today. Thanks for the '@%' tip. I'll put it to good use,