CODEWITHSUNDEEP
sqlteradata
Victor Khiz
Victor Khiz

Reputation: 3

Teradata Sub-string Before and After certain character

I am looking for a way to extract text that is before and after a certain character in Teradata. For example, I have a column that is consistent except for the word in the middle - which I want to extract.

I have these two examples:

  1. "Customer Activated Account DD: ABCD, ON DATE XXXX"
  2. "Customer Activated Account DD: EFGH, ON DATE XXXX"

I need the text that exists right after ":" and before "," - in this case ABCD or EFGH. So far I have SUBSTRING (column FROM POSITION ('DD: ' IN column) +4) but that returns everything after the ":" like ABCD, ON DATE XXXX.

Upvotes: 0

Views: 18422

Answers (2)

Pvham
Pvham

Reputation: 51

I agree that regexp_substr is better in this case. Would your delimiter however be fixed, strtok can be quite helpful. In your case this could work:

STRTOK(STRTOK(column,':',2),',',1)

The first argument is the input string/column; the second the delimiter; and the third the observation number n.

Upvotes: 0

Pradeep Khatri
Pradeep Khatri

Reputation: 96

You can use Regular expression to extract the information between ':' & ',' for the given requirement.

SELECT regexp_substr('Customer Activated Account DD: EFGH, ON DATE XXXX','[^:,]+',1,2);
SELECT regexp_substr('Customer Activated Account DD: ABCD, ON DATE XXXX','[^:,]+',1,2);

[^:,] is used to split the string and then second position word is the required output

Upvotes: 0

Related Questions