Thursday, January 31, 2013

String manipulations in Teradata

Recently, I got this mildly challenging problem, (challenges are always welcome :))
I had to manipulate a varchar field to pick a part of the string, luckily, it follows a pattern

Sample data:

qstn: explain in detail - description
qstn2: what is the nature of the issue - type
qstn3: wildcard

I had to pick only a part of the string, but I had the following restrictions:

1. Cannot pick a fixed length or from a fixed position
2. Had to look for anything between : and -
3. - may or may not be present
4. If it is not present, use till last character

This is what I wrote:



SELECT
CASE WHEN INDEX(string_manip_field,'-') =
THEN (LENGTH(string_manip_field)-INDEX(string_manip_field,':')) 
ELSE (INDEX(string_manip_field,'-') - INDEX(string_manip_field,':') + 1) 
END AS NUM_CHAR
,SUBSTR(string_manip_field,INDEX(string_manip_field,':')+1,NUM_CHAR-2)
 FROM {Tablename}

Executable format:

SELECT
CASE WHEN INDEX('qstn: explain in detail - description','-') = 0 
THEN (LENGTH('qstn: explain in detail - description')-INDEX('qstn: explain in detail - description',':')) --number of characters will be from : till end
ELSE (INDEX('qstn: explain in detail - description','-') - INDEX('qstn: explain in detail - description',':') + 1) --number of characters will be from : till hyphen
END AS NUM_CHAR
,SUBSTR('qstn: explain in detail - description',INDEX('qstn: explain in detail - description',':')+1,NUM_CHAR-2)

Any other suggestions welcome. This blog is open to discussion.