Both substring functions are fairly straightforward when working with character data. However, they are not limited to character columns. They can also be used with numeric data. The caution here is that these functions are designed to work with character data. Therefore, all numeric data is automatically converted to character before it can be used. The biggest issue is that Teradata uses the same type of implicit conversion that was seen in an earlier chapter. As a reminder, the database uses the full length required to store the largest possible data value for each numeric data type. The length must include one additional character for the sign in the event of a negative (−) number.
A value like −32,000 as a SMALLINT is "−32000" with a length of six characters (5 digits + sign). At the same time, a value like 128 is also assumed to be a SMALLINT as "128" with a length of six characters. The difference here is that the first three digits are spaces (sign is assumed positive and becomes a space).
However, if the −32,000 is stored in a column of INTEGER data type, it has a character length of eleven and is internally stored as "−32000" with five leading spaces. This must be taken into consideration when using substring functions with numeric data.
The next SELECT demonstrates this conversion using literal data:1 Row Returned
This example demonstrates the attention that must be applied when using a numeric data type with character functions. A safer way to do the above processing is the following SELECT:1 Row Returned
As seen in these examples, it is a good idea to use CAST to explicitly convert the numeric data to character data first. Then, the SUBSTRING or SUBSTR operations perform as expected.
Teradata Related Interview Questions
|Microstrategy Interview Questions||Informatica Interview Questions|
|MySQL Interview Questions||Oracle 11g Interview Questions|
|Hadoop Interview Questions||TeraData DBA Interview Questions|
|MYSQL DBA Interview Questions||Database Administration Interview Questions|
|DB2 SQL Programming Interview Questions||Hadoop Administration Interview Questions|
|Java Hadoop Developer Interview Questions||Informatica MDM Interview Questions|
|Informatica Admin Interview Questions||Hadoop Testing Interview Questions|
Teradata Related Practice Tests
|Microstrategy Practice Tests||Informatica Practice Tests|
|MySQL Practice Tests||Oracle 11g Practice Tests|
|Hadoop Practice Tests||TeraData DBA Practice Tests|
|MYSQL DBA Practice Tests||Database Administration Practice Tests|
|DB2 SQL Programming Practice Tests||Hadoop Administration Practice Tests|
Teradata Parallel Architecture
Fundamental Sql Using Select
On-line Help And Show Commands
Date And Time Processing
Character String Processing
Reporting Totals And Subtotals
Data Definition Language
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.