CODEWITHSUNDEEP
sqlregexamazon-redshift
Sai Kiran T
Sai Kiran T

Reputation: 73

redshift sql using regular expression to get first, second, third values from a string which is like following

pattern: d0=123;d1=134;d2=123;d3=13;d4=33;d5=44;d6=55;

If want to get the first number between = & ;

output: 
123

If I want to get the second occurrence between = & ;

output: 
134

regex used:

select  
  regexp_substr(' d0=123;d1=134;d2=123;d3=13;d4=33;d5=44;d6=55;','d[0-9]+=' ,1,2) as b;

but output is coming like this:

d1=

Any pointers in this direction will be helpful.

Upvotes: 3

Views: 3565

Answers (1)

Kaspa Phani Vardhan
Kaspa Phani Vardhan

Reputation: 82

Another approach would be to use regex_substr instead 

regex_substr(s,'[^d=;]*[0-9]+[^;=d]',1,2)

Increment/decrement the integer value 2 as needed.

Upvotes: 2

Related Questions