SQL Coalesce with Empty String

SQL Coalesce with empty string

Use a CASE expression or NULLIF:

SELECT COALESCE(NULLIF(Other,''),Industry) Ind FROM registration

Is there a similar function to COALESCE for empty string in AS400

You can use NULLIF():

SELECT COALESCE(NULLIF(value, ''), 'M')
FROM [My Table]

SQL coalesce with empty string ,NULLIF is not usable in my system

NULLIF() is not a standard SQL function, so it won't work depending on the brand of RDBMS you use.

The equivalent in standard SQL would be:

COALESCE(CASE columnname WHEN '' THEN NULL ELSE columnname END, 'THIS')

Or you might as well do this:

CASE columnname WHEN '' THEN 'THIS' ELSE columnname END

MySQL, IFNULL(), COALESCE() on String not replacing

COALESCE and IFNULL substitute only NULL values, your table seem to contain empty strings:

SELECT
  COALESCE(NULLIF(main_table.title_column, ''), 'no name') AS title
FROM main_table;

How to coalesce an empty/null value that is part of Concact string in Postgres?

I would recommend simply using string function concat_ws() for this: it ignores null value by design, so you just don't need to worry about them:

select 
    tbl_225.id,
    concat_ws('', substring(field_2::text, 0, 100), ' (' || field_1 || ')') as fullname
from schema_1.tbl_225;

Replace empty string in hive- Nvl and COALESCE tried

As you are having empty strings so when we use coalesce or nvl works only if we are having null values in the data. These functions won't work with empty strings.

With Empty strings:

hive> select coalesce(string(""),"1");
+------+--+
| _c0  |
+------+--+
|      |
+------+--+
hive> select nvl(string(""),"1");
+------+--+
| _c0  |
+------+--+
|      |
+------+--+

With null values:

hive> select coalesce(string(null),"1");
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+
hive> select nvl(string(null),"1");
+------+--+
| _c0  |
+------+--+
| 1    |
+------+--+

Try to alter the table and add this property

TBLPROPERTIES('serialization.null.format'='')

if this property doesn't display empty string as null's then we need to use either case/if statement to replace empty strings.

You can use if statement

if(boolean testCondition, T valueTrue, T valueFalseOrNull)

 hive> select if(length(trim(<col_name>))=0,'<replacement_val>',<col_name>) from <db>.<tb>;

Example:

 hive> select if(length(trim(string("")))=0,'1',string("col_name"));
    +------+--+
    | _c0  |
    +------+--+
    | 1    |
    +------+--+
hive> select if(length(trim(string("1")))=0,'1',string("col_name"));
    +-----------+--+
    |    _c0    |
    +-----------+--+
    | col_name  |
    +-----------+--+

Coalesce(null,'') gets null in Oracle and gets '' in SQL Server?

In Oracle document about NULL

Oracle Database treats a character value with a length of zero as null.

Oracle internally changes the empty string to NULL values. Oracle simply won't let insert an empty string.

select null from dual

the same as

select '' from dual

They are all return NULL.

So when you use select COALESCE (null,'') from dual it will translate to select COALESCE (null,null) from dual in Oracle.

sqlfiddle

Here is a link talk about this.

Why does Oracle 9i treat an empty string as NULL?

MySQL, coalesce equivalent for empty values?

Use the ANSI CASE statement/expression:

SELECT CASE 
         WHEN LENGTH(col) = 0 OR col IS NULL THEN 'banana'
         ELSE col
       END AS fruit

There's no boolean in SQL, or MySQL. MySQL actually stores the value as an INT, values zero or one:

SELECT CASE 
         WHEN col = 0 THEN 'banana'
         ELSE col
       END AS fruit

---

Related Topics