# Impala Miscellaneous Functions

### Impala Conditions with Example

Impala supports the following conditional functions for testing equality, comparison operators, and nullity:

‘Case’ Example:

1)  If else

select case when 20 > 10 then 20 else 15 end;

Output:  20

2) If else if

select case when 9 > 10 then 20 when 1 > 2 then 1.0 else 15 end;

Output:  15

=====================================================================================

‘Coalesce’ Function Example:

The COALESCE function in Impala returns the first non-NULL expression among its arguments.

Simple Example:

select coalesce (NULL, ‘b’, ‘c’);

Output:  b

select coalesce (‘a’, ‘Null’, ‘c’);

Output: a

USECASE: we want to find out the best way to contact each person according to the following rules:

1. If a person has a mobile phone, use the mobile phone number.
2. If a person does not have a mobile phone and has a home phone, use the home phone number.
3. If a person does not have a mobile phone, does not have a cell phone, and has a office phone, use the office phone number.

Create Table:

Insert Data  :

Query:

OUTPUT:

+——-+————————————–+————+

| name | coalesce (mobileno, homeno, officeno) | city       |

+——-+————————————–+————+

| user3 | 9874561                              | Vijayawada |

| user2 | 1234567                              | Chennai    |

| user1 | 6654756                              | Hyderabad  |

+——-+————————————–+————+

================================================================

DECODE Funcation Example:

Create Table:

>create table decodetable (empid INT, empname STRING, empcountry STRING, empage INT);

Insert Data:

Query:

Output:

+——-+———–+————+——–+

| empid | shortcode | empcountry | empage |

+——-+———–+————+——–+

| 14    | PN        | Bangalore  | 15     |

| 12    | RJ        | Delhi      | 35     |

| 13    | RH        | Chennai    | 40     |

| 11    | AZ        | Vijayawada | 21     |

+——-+———–+————+——–+

‘shortcode’ is the name given to the column with the DECODE statement

==================================================================================

ISNULL ():

If that column had null value, then it will replace with give value

Sample Table:

Query: select * from emp1;

+——-+———+————+——–+

| empid | empname | empcountry | empage |

+——-+———+————+——–+

| NULL | Azmal   | NULL       | 15     |

| 12   | Raj     | Delhi      | 35     |

| 11   | Ravi    | hyd        | 32     |

| 13   | Rahul   | chennai    | 40     |

| 14   | Phani   | Bangalore  | 15     |

+——-+———+————+——-+

ISNull Query:

select isnull (empid ,15) from emp1;

Output:

+——————-+

| isnull (empid, 15)|

+——————-+

| 12                |

| 15                |

| 13                |

| 11                |

| 14                |

+——————-+

==================================================================================

NULLIF ():

The syntax for the NULLIF function in Impala is:

NULLIF (expression1, expression2)

*expression1, expression2

The expressions that will be compared. Values must be of the same datatype.

*Note:

expression1 can be an expression that evaluates to NULL, but it can not be the literal NULL

Simple Examples:

SELECT NULLIF (‘Azmal Sheik’, ‘Azmal Sheik’);

Result: NULL

(returns NULL because values are the same)

Result: ‘hadooptutorial.info’ (returns first value because values are different)

SELECT NULLIF (12, 12);

Result: NULL

(returns NULL because values are the same)

SELECT NULLIF (12, 45);

Result: 12

(returns first value because values are different)

==================================================================================

NULLIFZERO:

It is one of the very important Impala functions converting zero to null value when divide-by-zero problem comes into picture.

Sample Table:

Query: select * from NullIfZeroTable;

+——-+————-+————+——–+

| empid | empname     | empcountry | empage |

+——-+————-+————+——–+

| 13    | Rahul       | Chennai    | 40     |

| 18    | Sheik Azmal | Hyderabad  | 0      |

| 14    | Phani       | Bangalore  | 15     |

| 12    | Raj         | Delhi      | 35     |

| 11    | Ravi        | Vijayawada | 32     |

+——-+————-+————+——–+

Fetched 5 row(s) in 0.36s

Query: select nullifzero (empage) from decodetable

Output:

+——————–+

| nullifzero(empage) |

+——————–+

| 32                 |

| NULL               |

| 40                 |

| 15                 |

| 35                 |

+——————–+

Fetched 5 row(s) in 0.37s

Related Functions:

ZEROIFNULL

Replace NULL values with 0

NVL ():

This function is used to replace NULL value with another value. It is similar to the IFNULL Function in Imapala and the ISNULL Function.

Query: select * from emp1;

+——-+———+————+——–+

| empid | empname | empcountry | empage |

+——-+———+————+——–+

| NULL | Azmal   | NULL       | 15      |

| 12   | Raj     | Delhi      | 35      |

| 11   | Ravi    | hyd        | 32      |

| 13   | Rahul   | chennai    | 40      |

| 14   | Phani   | Bangalore  | 15      |

+——-+———+————+——–+

Fetched 5 row(s) in 0.40s

Query: select null(empid,20) from emp1

+—————-+

|null (empid, 20)|

+—————-+

| 20             |

| 11             |

| 11             |

| 13             |

| 14             |

+—————-+

# Impala String Functions

Char_lenght ():

Returns the length in characters of the argument string. Aliases for the length () function.

Query:   select char_length(‘Impala’);

Output:

+—————————–+

| char_length(‘impala’)       |

+—————————–+

| 6                           |

+—————————–+

Concat ():

This function is used to concatenate two strings to form a single string.

Query:

select concat (empname, empcountry), empage, empcountry from emp1;

Output:

+—————————–+——–+————+

| concat (empname, empcountry) | empage | empcountry|

+—————————–+——–+————+

| PhaniBangalore              | 15     | Bangalore  |

| RahulChennai                | 40     | Chennai    |

| RajDelhi                    | 35     | Delhi      |

+—————————–+——–+————+

Find_in_set ():

FIND_IN_SET function returns the position of a string in a comma-delimited string list.

Query:

select FIND_IN_SET (‘b’, ‘a, b, c, d, e, f’);

Output:

+—————————————-+

| find_in_set (‘b’, ‘a, b, c, d, e, f’)  |

+—————————————-+

| 2                                      |

+—————————————-+

Fetched 1 row(s) in 0.01s

Repeat ():

Returns the argument string repeated a specified number of times.

Query:

select repeat (‘Azmal ‘ ,5);

Output:

+——————————-+

| repeat (‘azmal ‘, 5)          |

+——————————-+

| Azmal Azmal Azmal Azmal Azmal |

+——————————-+

Fetched 1 row(s) in 0.01s

For Reference take a look:

reverse (string a)

Purpose: Returns the argument string with characters in reversed order.

Return type: string

Purpose: Returns a string of a specified length, based on the first argument string. If the specified string is too short, it is padded on the right with a repeating sequence of the characters from the pad string. If the specified string is too long, it is truncated on the right.

Return type: string

rtrim (string a)

Purpose: Returns the argument string with any trailing spaces removed from the right side.

Return type: string

space (int n)

Purpose: Returns a concatenated string of the specified number of spaces. Shorthand for repeat (‘ ‘, n).

Return type: string

Strleft (string a, int num_chars)

Purpose: Returns the leftmost characters of the string. Shorthand for a call to substr () with 2 arguments.

Return type: string

Strright (string a, int num_chars)

Purpose: Returns the rightmost characters of the string. Shorthand for a call to substr () with 2 arguments.

Return type: string

substr (string a, int start [, int len]), substring (string a, int start [, int len])

Purpose: Returns the portion of the string starting at a specified point, optionally with a specified maximum length. The characters in the string are indexed starting at 1.

Return type: string

Translate (string input, string from, string to)

Purpose: Returns the input string with a set of characters replaced by another set of characters.

Return type: string

Trim (string a)

Purpose: Returns the input string with both leading and trailing spaces removed. The same as passing the string through both ltrim () and rtrim ().

Return type: string

Upper (string a), ucase (string a)

Purpose: Returns the argument string converted to all-uppercase.

Return type: string

=======================================================

Regexp_Extract ():