Question on SQL Query with NOT condition

• mcfarlandparkway

  SSCertifiable

  Points: 7701

  More actions

  May 14, 2025 at 10:16 pm

  #4591381

  I have a record, in those the three columns where firstname / last name and full name is empty.

  ID = 198765

  FirstName = empty

  LastName = empty

  FullName = empty

  Flag = 0

  I am trying to pull the record with ID 198765 out from select query where first and last or full name does not exists.

  select t.Id, t.flag from table t where t.flag = 0 and not (fullname = '' or fullname is NULL)

  I want to add first and last name condition too but when i added its not returning any data.

• Johan Bijnens

  SSC Guru

  Points: 135510

  More actions

  May 15, 2025 at 6:22 am

  #4591486

  remove the " not " from your where clause

  Johan
  
  Learn to play, play to learn !

  Dont drive faster than your guardian angel can fly ...
  but keeping both feet on the ground wont get you anywhere :w00t:

  - How to post Performance Problems
  - How to post data/code to get the best help[/url]

  - How to prevent a sore throat after hours of presenting ppt

  press F1 for solution, press shift+F1 for urgent solution

  Need a bit of Powershell? How about this

  Who am I ? Sometimes this is me but most of the time this is me

• Steve Collins

  SSCrazy

  Points: 2214

  More actions

  May 15, 2025 at 7:44 pm

  #4591805

  "I am trying to pull the record with ID 198765 out from select query where first and last or full name does not exists."

  What seems to be intended is for all 3 columns to pass a similar compound condition each of which must be TRUE for the same "record" (which in SQL is generally called a row).  The compound condition being: 1) the string isn't empty, and 2) the string isn't null

  select t.Id, t.flag 
  from (values (198765, '', '', '', 0),
               (198766, null, null, null, 0),
               (198767, null, '', null, 0),
               (198768, 'Joe', 'Smith', 'Joe Smith', 0)) t(ID, FirstName, LastName, FullName, Flag)
  where t.Flag  = 0 
        and not ((FirstName = '' or FirstName is NULL)
                  and 
                  (LastName = '' or LastName is NULL)
                  and 
                  (FullName = '' or FullName is NULL));

  I checked with AI and it came up with the same query.  That's usually a decent sign.  In this case the query could use LEN and CONCAT instead.  Unlike all the other scalar functions CONCAT (and CONCAT_WS) treats NULL as an empty string.  The query could exclude rows WHERE the character length (LEN) of the concatenated (CONCAT) string is greater than 0

  select t.Id, t.flag 
  from (values (198765, '', '', '', 0),
               (198766, null, null, null, 0),
               (198767, null, '', null, 0),
               (198768, 'Joe', 'Smith', 'Joe Smith', 0)) t(ID, FirstName, LastName, FullName, Flag)
  where t.Flag  = 0 
        and len(concat(FirstName, LastName, FullName))>0;

  Btw if you need a string concatenator which treats NULL like NULL use the "+" plus sign

  select concat(null, 'x'), null+'x', 'x'+'x';

  • This reply was modified 1 month ago by Steve Collins.

  Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können