madsthiru madsthiru - 2 months ago 25
SQL Question

Space handling in Teradata

I have the following rows in the table

Record_Value
E1X4B1 20160822
E1XBA1 20160822
E1 X920160822


I need to select the values X4,XB and X9. I wrote the query :

SELECT SUBSTR(Record_Value,3,2)


It selects only X4 and XB. To select the value X9 (which is in the 7th position) I thought of using the coalesce function but it handles only NULL values and not BLANK values. Can you please guide me. Expected Output would be

X4
XB
X9

Answer

Use TRIM to remove the leading blanks (and trailing...):

SELECT SUBSTR(TRIM(Record_Value),1,2)

Answer number 2, to the edited question, ANSI SQL compliant:

SELECT SUBSTRING(TRIM(SUBSTRING(Record_Value FROM 3)) FROM 1 FOR 2)