Routines: Scalar functions
A scalar function is a function that, for each set of one or more scalar parameters, returns a single scalar value. Examples of scalar functions include the LENGTH function, and the SUBSTR function.
Scalar functions can also be created that do complex mathematical calculations on function input parameters. Scalar functions can be referenced anywhere that an expression is valid within an SQL statement, such as in a selectlist, or in a FROM clause.
 Features

 Builtin scalar functions perform well.
 Builtin scalar functions are strongly typed.
 Can be referenced with SQL statements wherever expressions are supported.
 Logic is executed on the server as part of the SQL statement that references it.
 Output of a scalar UDF can be used directly by the statement that references the function.
 When used in predicates, scalar UDF usage can improve overall query performance. When a scalar functions are applied to a set of candidate rows at the server, it can act as a filter, thus limiting the number of rows that must be returned to the client.
 For external scalar userdefined functions, state can be maintained between the iterative invocations of the function by using a scratchpad.
 PARAMETER STYLE HIVE scalar functions that are used in predicates can be pushed down to the reader for better performance.
 Limitations

 By design, they only return a single scalar value.
 Transaction management is not supported within scalar functions. Commits and rollbacks cannot be executed within scalar function bodies.
 Result sets cannot be returned from scalar functions.
 In a single partition database userdefined external scalar UDFs can contain SQL statements. These statements can read data from tables, but cannot modify data in tables.
 In a multipartition database environment, userdefined scalar UDFs cannot contain SQL statements.
 Common uses

 To manipulate strings within SQL statements.
 To perform basic mathematical operations within SQL statements.
 Userdefined scalar functions can be created to extend the existing set of builtin scalar functions. For example, you can create a complex mathematical function, by reusing the existing builtin scalar functions along with other logic.
 Supported implementations

 Sourced implementation
 External implementation