Timeline for Best way to check for "empty or null value"
Current License: CC BY-SA 4.0
36 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Mar 11 at 22:02 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
format, clarify
|
| Jun 13, 2023 at 21:44 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
markup, format
|
| Jun 21, 2022 at 8:00 | comment | added | Rib47 |
Using "Postgres 11" and only original version with "coalesce" and "trim" (from question) works for space-filled strings for me: coalesce( trim(stringexpression),'')=''
|
|
| Jun 10, 2022 at 1:16 | comment | added | Erwin Brandstetter |
@Enissay: Simply stringexpression <> '' for "Not null Nor empty". That's already added in above.
|
|
| Jun 10, 2022 at 0:48 | comment | added | Enissay |
Google brought me here even though I was looking for the opposite case: Not null Nor empty... I wonder if you can add it as an fyi to complete the very nice answer... EDIT: well, it should be (stringexpression = '') IS FALSE xD
|
|
| Mar 18, 2022 at 13:51 | comment | added | godfryd |
Using (stringexpression = '') IS NOT FALSE is a bad advice because of poor readablity. It may be confusing for the reader as to what happens when stringexpression is NULL. Using coalesce fot this is a much better idea. Readability is important. If someone misunderstands your code it may result in a bug. A lot of code is read by multiple people, with varying experience, over multiple years.
|
|
| Jun 30, 2021 at 11:36 | comment | added | ayhan | Oh, I misunderstood that line. By saying "reverse approach", I thought "stringexpression is neither NULL nor empty" is meant, but I see that another way of expressing "stringexpression is either NULL or empty" is shown at that line. So that expression is correct. Sorry. | |
| Jun 30, 2021 at 10:50 | comment | added | Erwin Brandstetter | @ayhan: What's not true? | |
| Jun 30, 2021 at 9:14 | comment | added | ayhan | The expression "Or the reverse approach (may be easier to read): (stringexpression <> '') IS NOT TRUE" is not true. | |
| Jun 26, 2020 at 22:40 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
clarify, denoise, add quote: char(n) discouraged; update markup, links, fiddle
|
| May 22, 2020 at 16:29 | history | edited | Erwin Brandstetter | CC BY-SA 4.0 |
links
|
| Sep 6, 2017 at 14:32 | comment | added | Woody | You are right, coalesce will do the job for Oracle, thanks | |
| Sep 6, 2017 at 14:19 | comment | added | Erwin Brandstetter | @Woody: It will return NULL for NULL input. That's not what the question is asking for. This answer is for Postgres, not Oracle, where NULL and the empty string are considered the same - in violation of the SQL standard. | |
| Sep 6, 2017 at 14:10 | comment | added | Woody | The fastest queries you mention are not working on Oracle. This query will work on oracle and postgres : WHERE NOT(stringexpression <> ''); | |
| May 23, 2017 at 12:02 | history | edited | URL Rewriter Bot |
replaced http://stackoverflow.com/ with https://stackoverflow.com/
|
|
| Mar 6, 2017 at 22:40 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify
|
| Mar 6, 2017 at 16:51 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
deleted 12 characters in body
|
| Mar 6, 2017 at 16:32 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify trim, update fiddle, include demo
|
| Feb 18, 2016 at 10:46 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify
|
| Sep 8, 2015 at 13:46 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
fix format
|
| Aug 28, 2015 at 15:15 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify, links
|
| Jun 10, 2015 at 8:57 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clean up
|
| May 21, 2014 at 19:00 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify the addendum
|
| May 21, 2014 at 18:58 | comment | added | Erwin Brandstetter |
But coalesce(' '::char(5), '') = '' does not. I would use one of the top two expressions in any case, which work for any character type and are fastest and cleanest.
|
|
| May 21, 2014 at 18:17 | comment | added | Andrus |
select coalesce(' ', '') = '' returns false. So TRIM() is required
|
|
| May 21, 2014 at 14:54 | comment | added | Erwin Brandstetter | @Andrus: Yes, you can. I added that and some more to the answer. | |
| May 21, 2014 at 14:51 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
include feedback, expand fiddle, add more
|
| May 21, 2014 at 14:08 | comment | added | Andrus |
You wrote that any string expression containing only spaces is equal to '' . Can I remove trim and use coalesce(stringexpression,'')='' to check. This looks more readable to me compared to your answer.
|
|
| May 21, 2014 at 14:05 | vote | accept | Andrus | ||
| May 21, 2014 at 13:51 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
typo, clarify wording
|
| May 20, 2014 at 19:43 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
reduce the rant, typos
|
| May 20, 2014 at 19:42 | comment | added | Erwin Brandstetter |
@a_horse_with_no_name: OP asks for the best way to check if value is null or empty string. The trim() call is (comparatively) expensive - and just not necessary. I added more about char(n) and "empty string".
|
|
| May 20, 2014 at 19:38 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
clarify empty string in char(n)
|
| May 20, 2014 at 19:12 | comment | added | user330315 | Maybe I'm missing something, but it doesn't check for leading or trailing whitespace. | |
| May 20, 2014 at 18:52 | history | edited | Erwin Brandstetter | CC BY-SA 3.0 |
add link to manual
|
| May 20, 2014 at 18:47 | history | answered | Erwin Brandstetter | CC BY-SA 3.0 |