SQL Data Type Conversion Functions in SAP HANA with Syntax and Examples

SQL Data Type Conversion Functions in SAP HANA with Examples and syntax:-

The SQL Data Type Conversion function in SAP HANA contains something different then the SQL SERVER.Below all the SQL Data Type Conversion functions are explained one by one with examples and with syntax.

1.CAST(<Value/Column/Expression> AS <Data Type>): functions converts the given value to the specified data type.

Example: SELECT CAST(1234 AS NVARCHAR) AS “STRING” FROM DUMMY
Result: This converts the number 1234 to string.

Example: SELECT CAST(123.45 AS INTEGER) AS “INTEGER” FROM DUMMY
Result: This converts the decimal value 123.45 to integer 123 (decimals are always truncate towards zero).

2.TO_ALPHANUM(<Value>): converts a given value to an ALPHANUM data type.

Example: SELECT TO_ALPHANUM (10) “to alphanum” FROM DUMMY;
Result: Given value 10 will be converted to alpha numeric.

[AdSense-A]

3.TO_BIGINT(<Value>): This function converts the given value to a BIGINT data type.
Note: If the entered value has decimal points then these digits are truncated during conversion.

Example: SELECT TO_BIGINT(‘123’) AS “TO BIGINT” FROM DUMMY
 Result: This string 123 will be converted to 123 number.

 Example: SELECT TO_BIGINT(123.75) AS “TO BIGINT” FROM DUMMY
 Result: Decimal value 123.75 will be converted to number 123 and decimal values will be truncated.

4.TO_BINARY(<value>):  function is used to convert the given/source value to a BINARY data type.

Example: SELECT TO_BINARY(‘ABC’) AS “TO BINARY” FROM DUMMY
 Result: The binary value for ‘ABC’ is going to be 414243.

5.TO_BLOB(<value>): This function is used convert the given/source value to a BLOB data type.
Note: The source/given value must be a binary string.

Example: SELECT TO_BLOB (TO_BINARY(‘ABC’)) “TO BLOB” FROM DUMMY;
 Result: The result is going to ABC again because it will be converted to binary value which gain will be converted to characters to TO_BLOB function.

6.TO_CLOB(<value>): This function is used convert the given/source value to a CLOB data type.

Example: SELECT TO_CLOB(‘SAPSTUDENT.COM is learning resource for SAP HANA’) AS “TO CLOB” FROM DUMMY
 Result: The result is going to be same string with the data type CLOB.

[AdSense-A]

7. TO_DATE (<Value>,<Format>): This function is used to convert the date values stored in string format into HANA default date format ‘YYYY-MM-DD’. The input should the date string and format how it is stored. If the string date format is in the same format of HANA default or YYYY/MM/DD, then we can skip entering the date format in the input.

Example: SELECT TO_DATE(‘1/12/2015’, ‘MM/DD/YYYY’) “to date” FROM DUMMY;
 Result: 2015-01-12 (the source string date with format ‘MM/DD/YYYY’ has been converted HANA data format
 YYYY-MM-DD

 Example: SELECT TO_DATE(‘2015-01-12’) “to date” FROM DUMMY;
 Result: 2015-01-12. If the source string date value is in HANA default format, then we can skip the
 format in the input)

8.TO_DATS(<date string>): This function is used to convert the date string values to ABAP DATE string with format ‘YYYYMMDD’.
Note: The source date string value format should HANA default date type i.e ‘YYYY-MM-DD’. If the format is not like HANA default, then we need to use TO_DATE function before be apply TO_DATS.

 
Example: SELECT TO_DATS(‘2015-01-01’) AS “ABAP DATE” FROM DUMMY
 Result: This gives use result as ‘20150101’.

 Example: SELECT TO_DATS(TO_DATE(’01/01/2015′,’MM/DD/YYYY’)) AS “ABAP DATE” FROM DUMMY
 Result: TO_DATE function first converts source date into HANA format and then TO_DATS will used to convert it to ABAP format as shown in the above result.

9. TO_DECIMAL(<value>,<precision>,<scale>): This function is used to convert the given value to the specified precision and scale.

Example: SELECT TO_DECIMAL(1234.789,10,2) AS “TO DECIMAL” FROM DUMMY
 Result: 1234.78 (the scale is truncated to 2)

 Example: SELECT TO_DECIMAL(1234.789,10,0) AS “TO DECIMAL” FROM DUMMY
 Result: 1234 (The scale is defined as 0, so everything is truncated after precision)

10.TO_DOUBLE(<vale>): This function is used to convert the given string value to double data type.

Example: SELECT TO_DOUBLE(1234.78) AS “TO DOUBLE” FROM DUMMY
 Result: 1234.78

Example: SELECT TO_DOUBLE(‘1234.78’) AS “TO DOUBLE” FROM DUMMY
 Result: 1234.78

11.TO_FIXEDCHAR(<string>,<no of characters>): function is used extract the specified number of characters from input string.

Example: SELECT TO_FIXEDCHAR(‘sap student’,3) AS “TO FIXED CHAR” FROM DUMMY
 Result: sap (first 3 characters from the input string)

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.