Search code examples
teradatateradatasql

How to pick the string after last underscore in Teradata sql

I need to pick that portion of a string column in Teradata sql that appears after the last underscore. If the string is blank, it must return blank. The string can have multiple underscores.

input             output
abc_def_ghi         ghi 
ab_cd_ef_gh_ij      ij 
ab_cd               cd 
ab_11_22            22

How can I write to regex to capture the end section of such a string column?

Solution

  • instr can search backwards, instr(col, '_', -1) finds the last underscore:

    substr(col, instr(col, '_', -1) +1)

    Or a RegEx, which searches for characters between the last underscore and the end of the string:

    regexp_substr(col,'[^_]*$')

    Both solutions will return the full string if there's no underscore.

    If you change the RegEx to '_[^_]*$' it returns NULL if there's no underscore.