How to replace/convert empty string with NULL in SELECT query without altering the table or updating the table.
Asked
Active
Viewed 1.1k times
3 Answers
16
The NULLIF function is supported on most major RDBMS's.
SELECT NULLIF(MyColumn, '') FROM MyTable
This will return NULL for any row if MyColumn is equal to the empty string.
p.s.w.g
- 146,324
- 30
- 291
- 331
-
never used it but glad to know it. – T McKeown Apr 09 '14 at 18:33
-
This does not seem to actually replace the empty string value with null within the original table. – SedJ601 Jan 19 '17 at 17:29
-
3@SedrickJefferson Correct. `SELECT` statements will not modify the original table. You'd need to use an `UPDATE` for that; either use `UPDATE MyTable SET MyColumn = NULLIF(MyColumn, '')` or `UPDATE MyTable SET MyColumn = NULL WHERE MyColumn = ''` – p.s.w.g Jan 19 '17 at 17:37
1
SELECT CASE RTRIM(Column) WHEN '' THEN NULL ELSE Column END AS [Column]
T McKeown
- 12,971
- 1
- 25
- 32
-
-
I mean something like `SELECT magic(*) FROM table` or `SELECT mytable.* FROM mytable` because none of these things are working with that. – mega6382 Apr 09 '14 at 18:40
-
you have to specify which column you are testing to determine if it's empty. You can't apply logic to a wildcard. – T McKeown Apr 09 '14 at 18:42
-
-
2@mega6382 No, you'd have to handle each column individually. You probably shouldn't be using `SELECT *` anyway, see [Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc](http://stackoverflow.com/questions/65512/which-is-faster-best-select-or-select-column1-colum2-column3-etc) – p.s.w.g Apr 09 '14 at 18:44
-
@p.s.w.g Thanks for telling. Because it is always best practice to use faster methods. – mega6382 Apr 09 '14 at 18:51
