/build/static/layout/Breadcrumb_cap_w.png

SQL Question - custom_field_value is empty but not null

I've added custom_field_value16 to one of my ticket queues. It is hidden from ticket submitters and ticket owners. Based on an action in the ticket I drop a timestamp in the field. The intent is to check this field for the presence of the timestamp (because it's not always suppose to be present) and if it is present, use it in place of a system placed timestamp in another field.

In trying to query for the timestamp value I'm running into a problem where there is no timestamp present in custom_field_value16 but the field doesn't "= IS NOT NULL" because it is an empty string value as in = '' (two single quotes). I was trying to use ifnull(expr1,expr2) in my query but it doesn't return all of the rows where custom_field_value16 doesn't have a timestamp.

Question: Is there a suitable replacement to ifnull? I have tried using CASE WHEN () THEN () but it becomes very convoluted and doesn't provide accurate results.

1 Comment   [ + ] Show comment
  • have you tried
    filter line 1: does not begin with '' or does not contain ''
    or
    filter line 2: is not null - SMal.tmcc 9 years ago
    • Thank you for this. I didn't use DOES NOT but you started me thinking along a different line and it helped. Still ended up using CASE WHEN but since it is a text field I was able to add LIKE and NOT LIKE to look for the timestamp pattern. It's not very pretty but it works without negatively impacting the kbox when it runs ;)

      Thanks again - jmarotto 9 years ago

Answers (0)

Be the first to answer this question

Don't be a Stranger!

Sign up today to participate, stay informed, earn points and establish a reputation for yourself!

Sign up! or login

Share

 
This website uses cookies. By continuing to use this site and/or clicking the "Accept" button you are providing consent Quest Software and its affiliates do NOT sell the Personal Data you provide to us either when you register on our websites or when you do business with us. For more information about our Privacy Policy and our data protection efforts, please visit GDPR-HQ