DB2 field value replacement

1
votes

I have a DB2 employee table with a column called "Name" - varchar. it is not a primary key. 'Space' is also considered valid value here. some fields under this column has just space.

Now I need a query that fetches all the values in this employee table by replacing 'Space' with NULL or any other default value say 'NoName'.

One idea I have got, is to truncate the values with LTRIM function in SELECT, which will result 'empty string' and if I could find some string operation function that returns NULL with 'empty string' as input, I can use IFNULL of COALESCE to replace the NULL with 'NoName' as a result.

But I could not recall any such function. Can you help? Or is there any other way of accomplishing this ?

2
sqldatabasestringdb2
Simply COALESCE(columnname, 'NoName')? - jarlh
Have a constraint that prevents zero length names. (And also <space> names.) - jarlh
@jarlh coalesce wont 'remove' 'spaces' but only NULLs - DDS
@DDS, I know. But why allow space names? (See my second comment.) - jarlh
@jarlh because those are already there according to OP - DDS

2 Answers

2
votes

You can use something like:

Select case when <your column> = ' '    -- replace spaces
              or <your column> is null  -- replace nulls
              or <your column> = ''     -- replace empty strings
       then 'no data'                   -- with string 'no data'
       else <your column>               -- if 'regular name' returns it
       end as '<your column>'           -- gives the column a meaningful title
from yourtable

when the column in 'space' or NULL or empty string it returns the string 'no data' otherweise it returns the actual value

1
votes
select name, coalesce(nullif(name, ''), 'NoName') name_new
from table (values ' ', null, '', 'a') t(name);

NAME NAME_NEW
---- --------
     NoName
-    NoName
     NoName
a    a