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
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.
The next SELECT uses the Employee table to incorporate the concatenation:5 Rows Returned
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
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 Returned
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 Returned
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 Returned
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.
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.