Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2003
    Posts
    41

    Unanswered: anyway to check if a text field is blank (not null but just a empty string '') ?

    i have a trigger on a table right now... when fields are inserted, theres a text field inserted and i want to check if that text field = '' (the empty string, not NULL) and if it doesn't equal that, then perform some row updates on other tables, but if it is empty, to not do anything else in the trigger... right now i have this:

    Code:
    IF ((SELECT Note FROM XATPoDetail WHERE ReqNbr = (SELECT ReqNbr FROM Inserted)) LIKE(''))
    Note is the text field, XATPoDetail is the table where its being inserted into. I had to do the select FROM the table because it wouldn't let me select a text data type from the "Inserted" virtual table

    but it tells me me "Error 279: The text, ntext, and image data types are invalid in this subquery or aggregate expression"

    thanks

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    if exists (select 1 from XATPoDetail x inner join inserted i on x.ReqNbr=i.ReqNbr where cast(Note as varchar(8000)) = '')
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Dec 2003
    Posts
    41

    Talking

    Quote Originally Posted by rdjabarov
    if exists (select 1 from XATPoDetail x inner join inserted i on x.ReqNbr=i.ReqNbr where cast(Note as varchar(8000)) = '')
    wow thank you so much!!! ive been messing w/ this all day!

    i didn't think of that at all.. in my trigger i have a part where i need to copy a text field from one table to another and i had to use table aliases as well.. is this the case all the time that whenever i'm using the text data type and comparing it w/ something else i have to use table aliases?

    also, what does "select 1" mean? does that mean it will only select 1 field even if the query returns more than 1 row? (my query would return 1 row all the time becuase of the reqnbr constraint)

    one last thing.. when casting the Text data type and casting it as a varchar, does varchar(8000) mean 8000 would be the max length of the varchar?

    thanks a lot, i really appreciate the help

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    1. When comparing TEXT datatype you don't need to use table alias. I do it to shorten the statement. You can write it like this and it will work the same:

    if exists (select 1 from XATPoDetail inner join inserted on XATPoDetail.ReqNbr=inserted.ReqNbr where cast(Note as varchar(8000)) = '')

    2. "select 1" can also be rewritten as "select 'OK'" or "select *" etc. It really doesn't matter what you SELECT inside IF EXISTS (...) construct, as long as the SELECT returns non-empty result set IF EXISTS (...) will evaluate to TRUE.

    3. CAST(<text_field> as varchar(8000)) will take the first 8000 bytes of the value stored in the TEXT field and convert them to VARCHAR datatype.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •