-1

I want to convert null values to ' '. But when I use this code I got null values as 'NULL':

SELECT NVL(column_a, ' ') FROM table_a
APC
  • 144,005
  • 19
  • 170
  • 281
phileoseda
  • 292
  • 1
  • 6
  • 29
  • 1
    empty string is not the same as a space. empty string is the same as null, so what do you want? – hotfix Mar 21 '19 at 14:42
  • 1
    column_a data type? – jarlh Mar 21 '19 at 14:45
  • 1
    Are you sure your table column doesn't actually contain the string literal `'NULL'`, rather than really being null? What do you see if select `lower(column_a)` or `column_a||'test'`, or perhaps most definitively `dump(column_a)` ? – Alex Poole Mar 21 '19 at 15:09
  • What IDE are you using? For instance SQL Developer displays null as `(null)`. Perhaps your client is doing something similar? – APC Mar 21 '19 at 15:31
  • The function works just fine so you have two possible choices. You actually have the value 'NULL' in the field or your IDE is removing white space. I use Toad and it shows a single space character when viewed in hex. – Jim Castro Mar 21 '19 at 15:51
  • I mean space already @hotfix – phileoseda Mar 22 '19 at 05:08
  • column_a data type is varchar @jarlh – phileoseda Mar 22 '19 at 05:11
  • I use Toad, I have value 'NULL' as a string, thank you all. – phileoseda Mar 22 '19 at 06:29
  • Then why have you accepted an answer that said something completely different? – Alex Poole Mar 22 '19 at 08:37
  • No, it says GUI (Toad) displays NULL values as a string 'NULL'. Also your answer is a comment. If you put your answer as "answer", I would accept it. – phileoseda Mar 22 '19 at 10:39

1 Answers1

1

If your GUI displays "NULL" for those values, it is the GUI setting, not Oracle value. Set it to something else (e.g. nothing, in your case).

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • Why would any GUI report a single space as `NULL`? (Wondering if the question might have changed significantly in the grace period as you answered within that? And hotfix's comment might also suggest that happened...) – Alex Poole Mar 21 '19 at 15:13
  • @AlexPoole - I thing hotfix's comment may be a red herring. The SO UI renders ' ' very close together, so it quite looks like an empty string. I applied markdown to verify that the OP does want to default null to a space. – APC Mar 21 '19 at 15:34
  • 1
    @APC - that was how I first interpreted the comment, but as the code block clearly has a space, and this answer doesn't make sense for a space either (as the code can't return null - unless I'm missing something!?), I wondered if the OP originally did have `''` in the code then fixed it. – Alex Poole Mar 21 '19 at 15:39
  • It was space. In spite of the other character 'xxx', it didn't work. Problem is releated with 'NULL' value as a string. – phileoseda Mar 22 '19 at 06:35