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.

6 comments:

  1. Good one.. thanks for this..

    I've a scenario..

    Do we have something of a LEAST funtion of Oracle in Teradata?

    SELECT LEAST(1,2,3); would give me the ans as 1

    I know we can do a CASE WHEN but the number of parameters would keep changing and thought if there's something similar in TD?

    ReplyDelete
  2. If the data is like Arvy:abcd then the output should be abcd right? But if we do the above calculation it will get
    SUBSTR('Arvy:abcd',6,2) then the output will be 'ab' only right.. Please correct me if i am wrong..

    ReplyDelete
  3. Thanks a lot Meena for sharing.

    ReplyDelete
  4. Thank you, Sandy.
    @itaintme:
    We do have min() function, can you elaborate on what you are trying to achieve, I can help you better.

    ReplyDelete
  5. @aravinda:
    if you have a variable length string to manipulate, it is always best to find the number of characters in the string using length() function. If you want only 2 characters, then you can hardcode the value as you have suggested.
    Hope this helps.

    ReplyDelete