0

Is there any function to replace NULL or empty space with special character in hive? when I execute the below statement it returns a semantic exception stating trim works only on string/nvarchar

CASE
 WHEN TRIM(q.address) = '' OR q.address IS NULL THEN '?'
 ELSE q.address END as address

Please help.

Nishu Tayal
  • 20,106
  • 8
  • 49
  • 101
Holmes
  • 1,059
  • 2
  • 17
  • 25
  • Can you paste the larger statement you are trying to execute? What are the data types of the fields in your table? – Jared Jun 09 '16 at 14:29

2 Answers2

1

Use LENGTH() to check the length of the column value. It returns > 0, if there is some value else return 0 for empty or NULL value.

Also frame the column value in CASE WHEN ... END block

The final query may look like:

SELECT CASE WHEN LENGTH(address) > 0 THEN address ELSE '?' END AS address
FROM table_name;

Please refer Replace the empty or NULL value with specific value in HIVE query result

Hope this help you!!!

Community
  • 1
  • 1
Farooque
  • 3,616
  • 2
  • 29
  • 41
0

In order to replace nulls you can use Coalesce

Coalesce( q.address, '?')

But it seems your field adress is not of the proper type to use trim, can you show us the type of that field?

SCouto
  • 7,808
  • 5
  • 32
  • 49