CODEWITHSUNDEEP
sql
WouldBeNerd
WouldBeNerd

Reputation: 687

Why does my SQL query return a record that doesn't match the where clause?

This query returns a result of one record where both the [E-mail] and [E-mail 2] are equal to 'e'. Most definitely the result of a lazy salesman. Any idea why this is the case though?

My query is as shown below so I should only get records where the email is equal to '[email protected]' correct?

declare @email as varchar
set @email = '[email protected]'

select
    C.[No_],
    C.[First Name] firstname, 
    C.[Surname] lastname, 
    C.[E-Mail] as email, 
    C.[E-Mail 2] as email_2, 
    C.[GDPR Opt-in] as GDPR_opt_in,
    C.[Salesperson Code] as sales_person    
from 
    [Contact] as C
where  
    lower(C.[E-Mail]) = lower(@email)
    or lower(C.[E-Mail 2]) = lower(@email)

Upvotes: 1

Views: 45

Answers (2)

WouldBeNerd
WouldBeNerd

Reputation: 687

I got it. So i declared the variable like so and then it worked

declare @email VARCHAR(255)

sorry for bothering you stakoverflow!

Upvotes: -1

Jacobm001
Jacobm001

Reputation: 4539

You've declared @email to be a VARCHAR, meaning a single VARCHAR. When you're setting the @email variable, it's being truncated to just the first character.

Try changing VARCHAR to something like VARCHAR(100).

Upvotes: 4

Related Questions