Conversion functions

Conversion functions provide error-free type cast from inputs of any type to their target type.

These functions are handy in writing UDF function, since SQL TRY/CATCH are not permitted in UDF functions.

They are also useful in SELECT queries to cast a column to a desired type when we want no exceptions to be raised for invalid values.

      CREATE OR ALTER FUNCTION chm.ToInt64(@x SQL_VARIANT NULL, @default BIGINT NULL)
RETURNS BIGINT

CREATE OR ALTER FUNCTION chm.ToInt32(@x SQL_VARIANT NULL, @default INT NULL)
RETURNS INT

CREATE OR ALTER FUNCTION chm.ToInt16(@x SQL_VARIANT NULL, @default SMALLINT NULL)
RETURNS SMALLINT

CREATE OR ALTER FUNCTION chm.ToDecimal(@x SQL_VARIANT NULL, @default NUMERIC (18) NULL)
RETURNS NUMERIC (18)

CREATE OR ALTER FUNCTION chm.ToDouble(@x SQL_VARIANT NULL, @default FLOAT (53) NULL)
RETURNS FLOAT (53)

CREATE OR ALTER FUNCTION chm.ToSingle(@x SQL_VARIANT NULL, @default REAL NULL)
RETURNS REAL

CREATE OR ALTER FUNCTION chm.ToString(@x SQL_VARIANT NULL, @default NVARCHAR(MAX) NULL)
RETURNS NVARCHAR(MAX)

CREATE OR ALTER FUNCTION chm.ToChar(@x SQL_VARIANT NULL, @default NCHAR (1) NULL)
RETURNS NVARCHAR(MAX)

CREATE OR ALTER FUNCTION chm.ToByte(@x SQL_VARIANT NULL, @default TINYINT NULL)
RETURNS TINYINT

CREATE OR ALTER FUNCTION chm.ToBoolean(@x SQL_VARIANT NULL, @default BIT NULL)
RETURNS BIT

CREATE OR ALTER FUNCTION chm.ToDateTime(@x SQL_VARIANT NULL, @default DATETIME NULL)
RETURNS DATETIME

CREATE OR ALTER FUNCTION chm.ToGuid(@x SQL_VARIANT NULL, @default UNIQUEIDENTIFIER NULL)
RETURNS UNIQUEIDENTIFIER

CREATE OR ALTER FUNCTION chm.ToShort(@x SQL_VARIANT NULL, @default SMALLINT NULL)
RETURNS SMALLINT

CREATE OR ALTER FUNCTION chm.ToInt(@x SQL_VARIANT NULL, @default INT NULL)
RETURNS INT

CREATE OR ALTER FUNCTION chm.ToLong(@x SQL_VARIANT NULL, @default BIGINT NULL)
RETURNS BIGINT

CREATE OR ALTER FUNCTION chm.ToInt64Nullable(@x SQL_VARIANT NULL, @default BIGINT NULL)
RETURNS BIGINT

CREATE OR ALTER FUNCTION chm.ToInt32Nullable(@x SQL_VARIANT NULL, @default INT NULL)
RETURNS INT

CREATE OR ALTER FUNCTION chm.ToInt16Nullable(@x SQL_VARIANT NULL, @default SMALLINT NULL)
RETURNS SMALLINT

CREATE OR ALTER FUNCTION chm.ToDecimalNullable(@x SQL_VARIANT NULL, @default NUMERIC (18) NULL)
RETURNS NUMERIC (18)

CREATE OR ALTER FUNCTION chm.ToDoubleNullable(@x SQL_VARIANT NULL, @default FLOAT (53) NULL)
RETURNS FLOAT (53)

CREATE OR ALTER FUNCTION chm.ToSingleNullable(@x SQL_VARIANT NULL, @default REAL NULL)
RETURNS REAL

CREATE OR ALTER FUNCTION chm.ToCharNullable(@x SQL_VARIANT NULL, @default NCHAR (1) NULL)
RETURNS NVARCHAR(MAX)

CREATE OR ALTER FUNCTION chm.ToByteNullable(@x SQL_VARIANT NULL, @default TINYINT NULL)
RETURNS TINYINT

CREATE OR ALTER FUNCTION chm.ToBooleanNullable(@x SQL_VARIANT NULL, @default BIT NULL)
RETURNS BIT

CREATE OR ALTER FUNCTION chm.ToDateTimeNullable(@x SQL_VARIANT NULL, @default DATETIME NULL)
RETURNS DATETIME

CREATE OR ALTER FUNCTION chm.ToGuidNullable(@x SQL_VARIANT NULL, @default UNIQUEIDENTIFIER NULL)
RETURNS UNIQUEIDENTIFIER

CREATE OR ALTER FUNCTION chm.ToShortNullable(@x SQL_VARIANT NULL, @default SMALLINT NULL)
RETURNS SMALLINT

CREATE OR ALTER FUNCTION chm.ToIntNullable(@x SQL_VARIANT NULL, @default INT NULL)
RETURNS INT

CREATE OR ALTER FUNCTION chm.ToLongNullable(@x SQL_VARIANT NULL, @default BIGINT NULL)
RETURNS BIGINT

    

They all receive input in SQL_VARIANT type (i.e. anything) and a @default value which will be used if their input was null or were not castable to the requested result type.

Example:

      select chm.ToInt('12', 0)   -- 12
select chm.ToInt('12a', -1)   -- -1
select chm.ToBoolean('1', 0)   -- 1
select chm.ToBoolean('true', 0)   -- 1
select chm.ToBoolean('True', 0)   -- 1
select chm.ToBoolean('TRUE', 1)   -- 0
select chm.ToBoolean('on', 0)   -- 1
select chm.ToBoolean('12', 0)   -- 1
select chm.ToBoolean('0', 1)   -- 0

    

As it is seen, ToBoolean() assumes non-zero numbers, true, True and on strings as true. Any other case of true string such as TRUE, TRue is assumed as false.