How to replace/convert empty string with NULL in SELECT query without altering the table or updating the table.
Add a comment
|
3 Answers
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.
3 Comments
T McKeown
never used it but glad to know it.
SedJ601
This does not seem to actually replace the empty string value with null within the original table.
p.s.w.g
@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 = ''SELECT CASE RTRIM(Column) WHEN '' THEN NULL ELSE Column END AS [Column]
6 Comments
mega6382
what about using something with
*mega6382
I mean something like
SELECT magic(*) FROM table or SELECT mytable.* FROM mytable because none of these things are working with that.T McKeown
you have to specify which column you are testing to determine if it's empty. You can't apply logic to a wildcard.
mega6382
hmmm thats interesting.
p.s.w.g
@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 |
SELECT CASE Length(Col) WHEN 0 THEN NULL ELSE Col END
