SQL: replace NULL with SPACE

Question

I want to create a fixed length flat file (separated by ','), but when a field has a null value, the record moves. Please see illustration below (incorrect Jenny and Roland records):

Source Table:

Name    Color  Balance Zip Code
------- ------ ------- --------
Melissa Orange $200.00 40240
Karl    Blue   $150.00 40884
Jenny   -null- -null-  45667
Roland  -null- $110.00 53366
Vincent Green  $285.00 45677

Output I want to get:

Correct_Ouput
----------------------------
Melissa,Orange,$200.00,40240
Karl   ,Blue  ,$150.00,40884
Jenny  ,      ,       ,45667
Roland ,      ,$110.00,53366
Vincent,Green ,$285.00,45677

Output I want to get:

Wrong_Output
----------------------------
Melissa,Orange,$200.00,40240
Karl   ,Blue  ,$150.00,40884
Jenny  ,,,45667
Roland ,,$110.00,53366
Vincent,Green ,$285.00,45677

I tried searching but I get the null to empty string result.

Please help. Thank you.

Turophile · Accepted Answer · 2015-02-26 09:50:35Z

2

Use the COALESCE(column,' ') function.

For some databases, you can use IFNULL or NVL. See this web page for more details.

edited Feb 26, 2015 at 9:50

answered Feb 26, 2015 at 9:42

Turophile

3,4151 gold badge15 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Lalit Kumar B · Accepted Answer · 2015-02-26 10:01:09Z

0

You could use NVL to replace the NULL value with required number of spaces to retain the format.

For example,

SQL> SELECT 'Karl' NAME,
  2    NVL('Blue', '     ') color,
  3    NVL('$150.00','         ') balance,
  4    40884 zip_code
  5  FROM dual
  6  UNION ALL
  7  SELECT 'Jenny' name,
  8    NVL(NULL, '     ') color,
  9    NVL(NULL,'         ') balance,
 10    45667 zip_code
 11  FROM dual
 12  /

NAME  COLOR BALANCE     ZIP_CODE
----- ----- --------- ----------
Karl  Blue  $150.00        40884
Jenny                      45667

SQL>

You could also use DECODE.

For example,

DECODE(column, NULL, ' ')

answered Feb 26, 2015 at 10:01

Lalit Kumar B

49.5k13 gold badges104 silver badges131 bronze badges

Collectives™ on Stack Overflow

SQL: replace NULL with SPACE

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related