How to get a substring until the nth occurance of a character

Database

How to get a substring until the nth occurance of a character

i have values as follows:

132256711832009^998^R^BCBSTX^2014-03-20

143115179584022^999^R^BCBSIL^2014-11-07

140795460911026^999^R^BCBSOK^2014-03-20

140782836024024^999^R^BCBSIL^2014-03-19

140796806094025^998^R^BCBSIL^2014-03-20

i need to get the string until3rd occurance of ^. How can i get the below results?

132256711832009^998^R

143115179584022^999^R

140795460911026^999^R

140782836024024^999^R

140796806094025^998^R


1 REPLY
Junior Contributor

Re: How to get a substring until the nth occurance of a character

Regular expressions are supported since TD14:

REGEXP_SUBSTR(col, '(.*?\^){2}(.*?)(?=\^)')