Cast Strings to Integer or Numeric Values
You can cast a string to an integer or numeric value on Azure Synapse that is compatible with Teradata conversion rules.
Hyper-Q synthesizes SQL that transforms string data according to the Teradata conversion rules, which allow for several non-standard formats. Consider the following sample Teradata queries.
SELECT CAST(t AS int) FROM (SELECT '1:23' AS t) x;
–and–
SELECT s FROM (SELECT '1:23' AS t, ‘aaa’ AS s) x WHERE t = 123;
The cast of t to int in these examples cannot be executed natively on Azure Synapse and will result in an error at runtime. However, using Hyper-Q, this operation succeeds and produces results compatible with the Teradata conversion logic. The additional logic may, however, affect the performance of queries. For this reason, Datometry has added a mechanism for database administrators to fine tune the translated queries.
To enable the casting of strings to integer or numeric values compatible with the Teradata conversion logic, use the Hyper-Q hint text_to_number.
The syntax for text_to_number is:
text_to_number = [ direct | emulated ]
where:
Parameter | Description |
|---|---|
| Hyper-Q synthesizes the |
| Applies full emulation according to Teradata conversion rules. |
The text_to_number hint is meant to simplify synthesized SQL in cases where the underlying text data is expected to conform to formatting that can be cast to numbers using the native implementation of CAST.
The format used in the above examples is not supported by a cloud data warehouse, hence, the statement will error out. For example:
SEL CAST(t as INT) FROM --@dtm_text_to_number=direct (SEL '1:23' AS t) x;
–and–
SELECT s FROM --@dtm_text_to_number=direct (SELECT '1:23' AS t, ‘aaa’ AS s) x WHERE t = 123;
These statements produce the following error:
Failure 3706 DTM3103: Failed to run query on the underlying database: Error Message: [Microsoft][ODBC Driver 17 for SQL Server][SQL Server] Conversion failed when converting the varchar value '1:23' to data type int. SQLSTATE IS: 22018, Backend Error Code: 245, SQL State: 22018 (odbc_beta:457)
Important
Datometry recommends that you test the text_to_number hint using sample SQL statements before applying it to production workloads.
You can specify text_to_number as a global setting in the Hyper-Q configuration file (dtm.ini).
If you want to apply text_to_number to all statements that cast a string to an integer or numeric value on Azure Synapse that is compatible with Teradata conversion rules, the preferred method is to specify it as a global setting. The text_to_number property is in the [gateway] section of the dtm.ini file, which defines the connection to the target data warehouse.
"gateway".text_to_number="direct"
To learn more about the dtm.ini file, see Example Hyper-Q Configuration File for Azure Synapse.
To disable the Teradata CAST behavior, specify the following Hyper-Q hint at the request level.
SELECT --@dtm_text_to_number=direct CAST(t AS int) FROM (SELECT SQL int '123' AS t) x;
The hint must be enclosed in the SQL statement to prevent the client querying the database from removing the comment before sending the statement to Hyper-Q. Regardless of its location in the statement text, the hint applies to the entire statement.
Note
The hint does not apply to individual casts in the statement, and the use of different settings for one hint within one statement are not supported.
To enable the Teradata CAST behavior, specify the following Hyper-Q hint at the request level.
SELECT --@dtm_text_to_number=emulated CAST(t AS int) FROM (SELECT SQL int '123' AS t) x;