Search code examples
teradatateradatasql

TeraData REGEXP_SUBSTR date extraction from API Field

Community! I am trying to get this syntax to work and I need some assistance. I need to remove everything in the API field and just get the date in the column. I am struggling to accomplish this and not sure how to write this. Any rewrites would be appreciated. Thanks Lads!

[EXTENDED_DATA] API Field: {"redeterminationDate":"2023-01-01 12:00:00"}

Output Needed as date: 2023-01-01

,REGEXP_SUBSTR(EXTENDED_DATA,'[^.-,]+[^0-9]+[^.-,]+[^0-9]+[^.-,]+[^0-9]'1,9) AS DATE
Solution

  • If that's the consistent structure, you can just substring it out. We can use instr to find the second colon and add 2 to get our starting position:

    substr(<column>,instr(<column>,':',1,2)+2,10)