Concatenation of Character Strings Teradata

Other examples of using POSITION or INDEX with SUBSTRING are presented below. However, to fully understand them, concatenation must be discussed.

Concatenation is the process of taking two or more columns and combining them into a single column. This is also a character operation. Math is used to combine numbers, not concatenation. However, if number columns are concatenated, they must first be converted to a character string.

The || is used in SQL to concatenate columns. The following uses literals to demonstrate its use:

SELECT 'ABC' || 'XYZ' AS One_column ; 1 Row Returned

One_column

ABCXYZ

Since they are combined into a single column, they need only one alias name.

The Department table is used with the Employee table to show more capabilities of concatenation.

One_column

The next SELECT uses the Employee table to incorporate the concatenation:

5 Rows ReturnedSELECT uses the Employee table to incorporate the concatenation

Each of the rows above is selected because the name of the department has more than one word in it, based on a space in the department name found by the WHERE clause. The POSITION function found them. Then, the INNER JOIN matched the department to the employees working there.

Now, let's say that it has been determined that the entire name is too long. To make matters worse, the name should be sorted by the last name, not the first. So the SELECT has been converted below to incorporate these changes:

5 Rows Returned

SELECT has been converted below to incorporate these changes

SELECT has been converted below to incorporate these changes

It has been determined the answer set is better. The POSITION function found the space in the name of the department and subtracted 1 from it. This provides the length of the first word and is used in the FOR portion of the SUBSTRING. Thus, only the first word of the department name is returned.

At the same time, it is not exactly what is needed. The last name should come before the first name and be separated by a comma:

5 Rows ReturnedLast name should come before the first name and be separated by a comma

Two changes were made to the SELECT. First, the last name is a fixed length character column so it needed to be trimmed. Second, the concatenation reversed the order of the names and added a comma between the last name and the first name. Lastly, the TITLE was used to eliminate the column heading above the portion of the department name.

As mentioned earlier, the true power of some functions becomes fully available when they are combined. Remember, although this book tends to present one or two capabilities at a time, it is done to enhance learning the functionality of each and to make suggestions as to their combined use in a production environment. Every production environment is unique and it is impossible to address them all.

Returning the first word with SUBSTRING has been demonstrated. However, what if the second or third word is needed. The next SELECT shows how to return from the second word to the end:

3 Rows ReturnedSELECT shows how to return from the second word to the end

Returning the second word with SUBSTRING is really nothing more than using the POSITION function in the FROM instead of the FOR. However, what if only the third word is needed? The next SELECT shows how to return the third word:

1 Row Returnednext SELECT shows how to return the third word

In order to accomplish this processing, everything from the second word to the end must be presented to the POSITION function in the WHERE clause to verify a third word. Once a row with a third word is found, the SUBSTRING in the SELECT uses the POSITION of the first space and the POSITION of the second space as a starting point and returns all characters to the end.


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

Teradata Topics