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:
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.
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.