Sql Function 在hive, Netezza, Oracle, Redshift之间的相互转换

---

Concat Function

Hive

string concat(string|binary A, string|binary B…)
Note that this function can take any number of input strings.

concat(‘foo’, ‘bar’) => return ‘foobar’
concat('A',null,'B')  => reuturn NULL

Netezza

select 'A' || 'B' || 'C' => return ABC
concat('A',null,'B')  => reuturn NULL

Oracle

select 'A' || 'B' || 'C' from dual => return ABC
select 'A' || null || 'B' from dual  => reuturn AB

Redshift

select 'A' || 'B' || 'C' => return ABC
concat('A',null,'B')  => reuturn NULL

---

Date to Timestamp Or create the end of the day

Hive

CAST(CONCAT('2016-Feb-09',' 23:59:59.0') AS timestamp)

Netezza

TO_TIMESTAMP('2016-Feb-22' || ' 23:59:59','yyyy-Mon-dd HH24:MI:SS')

Oracle

TO_DATE('2016-Feb-22','yyyy-Mon-dd')+(86399/86400)

Redshift

CAST('2016-Feb-22' || ' 23:59:59.0' AS timestamp)

---

DECODE

Hive

IF(wntrk = 1 , 'Y' , 'N')

Netezza

decode(wntrk , 1 , 'Y' , 'N')

Oracle

decode(wntrk , 1 , 'Y' , 'N')

Redshift

DECODE ( expression, search, result [, search, result ]… [ ,default ] )
An optional default value that is used for cases when the search condition fails. If you do not specify a default value, the DECODE expression returns NULL.(如果没有设置default, 会返回NULL)

decode(wntrk , 1 , 'Y' , 'N')

---

SUBSTRING FUNCTION

Hive

select substr('caterpillar',6,4)

Netezza

select substr('caterpillar',6,4) from sometable

Oracle

select substr('caterpillar',6,4) from dual

Redshift

SUBSTRING(string, start_position, number_characters )

select substring('caterpillar',6,4)