SUBSTRING and POSITION or INDEX Used Together Teradata

When two of these functions are used in combination, they deliver a lot of power to the SQL statement. The utilization below illustrates the ability to change one character to another character based on the stored data. Assume that the data should have been stored as an ‘M’ for male and an ‘F’ for female. However, due to a misunderstanding, the values have been reversed in every row stored in the table. How would you fix this?

This change can be accomplished using SQL. The following UPDATE statement manages it very well:

UPDATE Mytable
SET Gender_column=SUBSTRING('FM', POSITION('F' IN Gender_column)+1, 1 );

If this looks too simple to work, you need to look at it again. There is not always a need to be long and involved in order to accomplish a big task. What does the SQL do?

First, it examines the data stored in every row of Mytable. When the Gender_column contains an ‘F’, the POSITION function returns a 1 as the starting location of the only character stored there. Then, it adds 1 to the 1 (POSITION value) to calculate the value 2. It uses the 2 in the SUBSTRING function to return the character starting in position 2 of the literal string ‘FM’ or the ‘M’. Therefore, this row that was an ‘F’ becomes an ‘M’ and this now reflects a female instead of a male.

That is great for the females, but what about the ‘M’ values being converted to an ‘F’? It works the same way, but with different values being returned from the POSITION functions. Let's walk through this scenario. When the Gender_column contains an ‘M’, the POSITION function returns a 0 because the ‘F’ is not found. Then, it adds 1 to the 0 to calculate the value 1. It uses the 1 in the SUBSTRING function to return the character starting in position 1 of the literal string ‘FM’ which is the ‘F’ and converts this row from a male into a female.

Similar processing can be accomplished for more than a single character or multiple concurrent characters. Make sure that all multiple character values are the same length, even if literal spaces must be added at the end of the string.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

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

Teradata Topics