SUBSTRING Teradata

Compatibility: ANSI

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 ReturnedSUBSTRING

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

column1

ABC DE

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

Last_two

DE
H
L
OP

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.


All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Teradata Topics