Search code examples
sqlregexteradata

TERADATA REGEXP_SUBSTR Get string between two values

I am fairly new to teradata, but I was trying to understand how to use REGEXP_SUBSTR

For example I have the following cell value = ABCD^1234567890^1

How can I extract 1234567890

What I attempted to do is the following:

REGEXP_SUBSTR(x, '(?<=^).*?(?=^)')

But this didnt seem to work.

Can anyone help?

Solution

  • It might (or might not) be possible to use REGEXP_SUBSTR() to handle this, but you would need to use a capture group. An alternative here would be to do a regex replacement instead:

    SELECT x, REGEXP_REPLACE(x, '^.*?\^|\^.*$', '') AS output
FROM yourTable;

    The regex pattern used here matches:

    • ^.*?\^ everything from the start to the first ^
    • | OR
    • \^.*$ everything from the second ^ to the end

    We then replace with empty string to remove the content being matched.