2 Replies - 822 Views - Last Post: 26 August 2012 - 12:19 PM Rate Topic: -----

#1 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 89
  • View blog
  • Posts: 582
  • Joined: 14-September 11

Simple Query including character it shouldn't

Posted 26 August 2012 - 11:42 AM

I have a query to return the names of vendors, their names are split into first name and last name fields. I am trying to return vendors whose last name begins with A, B, C, or E. Here is my query:

SELECT VendorContactLName + ', ' + VendorContactFName AS 'Full Name'
FROM Vendors
WHERE VendorContactLName < 'F' AND VendorContactLName <> 'D'
ORDER BY VendorContactLName, VendorContactFName


It is returning ALL the names under F, including ones whose last name starts with a 'D', I can't figure out why, it seems like a pretty straightforward condition

Is This A Good Question/Topic? 0
  • +

Replies To: Simple Query including character it shouldn't

#2 Jstall  Icon User is offline

  • Lurker
  • member icon

Reputation: 434
  • View blog
  • Posts: 1,042
  • Joined: 08-March 09

Re: Simple Query including character it shouldn't

Posted 26 August 2012 - 12:15 PM

Hi,

Well this:
VendorContactLName < 'F'



Is looking for anything that is less than F while this
VendorContactLName <> 'D'


Is comparing to the literal 'D' you are checking if the value is 'D' and only that.

With the data:
Apple
Banana
Cherry
D 
Doughnut 
Egg



You would get:
Apple
Banana
Cherry
Doughnut 
Egg



You could use LIKE to check if the value begins with D:
SELECT VendorContactLName + ', ' + VendorContactFName AS 'Full Name'
FROM Vendors
WHERE VendorContactLName < 'F' AND VendorContactLName NOT LIKE 'D%'
ORDER BY VendorContactLName, VendorContactFName



Hope this helps :)
Was This Post Helpful? 1
  • +
  • -

#3 synlight  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 89
  • View blog
  • Posts: 582
  • Joined: 14-September 11

Re: Simple Query including character it shouldn't

Posted 26 August 2012 - 12:19 PM

Ahhhhh!! That makes sense!! I'm going to go read up on 'LIKE'..

Thanks so much for the help!!! :bigsmile:

View PostJstall, on 26 August 2012 - 02:15 PM, said:

Hi,

Well this:
VendorContactLName < 'F'



Is looking for anything that is less than F while this
VendorContactLName <> 'D'


Is comparing to the literal 'D' you are checking if the value is 'D' and only that.

With the data:
Apple
Banana
Cherry
D 
Doughnut 
Egg



You would get:
Apple
Banana
Cherry
Doughnut 
Egg



You could use LIKE to check if the value begins with D:
SELECT VendorContactLName + ', ' + VendorContactFName AS 'Full Name'
FROM Vendors
WHERE VendorContactLName < 'F' AND VendorContactLName NOT LIKE 'D%'
ORDER BY VendorContactLName, VendorContactFName



Hope this helps :)

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1