The SUBSTRING function is used to retrieve a portion of the data stored in a column. Earlier, we saw the CAST function and that a column's data could be converted and that conversion might include truncation, allowed in Teradata mode.
There are potential problems associated with the CAST function. First, ANSI mode does not allow truncation and second, it only allows for the return of one or more characters at the beginning of the string, not the last or center characters. When characters other than the first ones are needed, or the session is in ANSI mode, the SUBSTRING is the correct SQL function to use. Like other functions, when using SUBSTRING, the name of the column is passed to the function along with the starting character location for the retrieval and lastly, the number of characters to retrieve (length).
The syntax of the SUBSTRING follows:
SUBSTRING(<column-name> FROM <start-location> [ FOR <length> ] )
Notice that the FOR is optional. When it is omitted, the length defaults to the end of the data, including the last character.
The next SELECT uses literal data as input to demonstrate the functionality of the SUBSTRING to what is returned:1 Row Returned
In this output, there are two columns that deserve some additional discussion. First, BeforeF6 starts at −1 and returns 6 characters. Since the "P" of "Partners" is in position 1, one position to the left is 0 and −1 is two positions to the left (prior to the "P"). Therefore, these two positions plus "Part" account for six characters.
The second discussion point is for column F6None. It returns no output. This is due to the fact that a 0 is specified for the length. It is not an error and a zero length string is returned. A character string with a length of 0 is different than a NULL.
At this point, a logical question is: why would anyone code the SUBSTRING with numbers like, −1 as seen in BeforeF6. The reason is that sometimes, these FOR and FROM values come from other programs, shell scripts or SQL functions (seen later in this chapter). Anytime variables are used, the content is not always strictly controlled. Therefore, the command will execute without failing.
To this point in this chapter, the emphasis is on the use of SUBSTRING in the SELECT. Once this is understood, it is easier to apply the same logic to its use in the WHERE clause. Whenever the decision of whether or not to read a row is made on a partial string comparison, SUBSTRING is another available tool to use.
The following incorporates the SUBSTRING into the WHERE clause:1 Row Returned
Since I love to combine functions, consider a situation where the last couple of characters are needed from a string. The key is to determine the length of the string. Hopefully, you just thought of the CHARACTERS function. If so, I applaud you.
The following SELECT uses TRIM, CHAR and SUBSTRING to display the last two characters of column1:4 Row Returned
It is important in this request to TRIM TRAILING instead of BOTH. Otherwise, the wrong length is used when there are LEADING spaces. An alternative is to TRIM column1 in both the SUBSTRING and the FROM.
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.