Extract left part of field with regexp_substr after two specific charaters

Question

I would like to create a new field on my table with only the part at the end of another field ("stop_name") which contains things as 'STOP_01_1_S1'.

I want to extract what is on the right of '_S'.

I've tried every single expression I managed to find in Stackexchange without success.

Answer 1

substr("fieldname",regexp_match("fieldname",'_S')+2)

regexp_match fetches the starting position of the string you're looking for. substr returns a part of a string starting from a certain position and maybe of a specific length. In this case you need to start your substring where your _S begins plus 2, so after it.

Answer 2

right ( "stop_name", length("stop_name")-regexp_match("stop_name" ,'(_S\\d+)')-1)

Answer 3

You can use

regexp_replace( 'STOP_01_1_S12','.*(_S)(.*)','\\2')

.*: any number of characters (we need to select them else they won't be removed)
(_S)(.*): find a "_S" and save as capture group 1. Followed by by anything and save as capture group 2.
\\2: replace the whole thing by the 2nd capture group, i.e. what comes after _S

Let's not that this method takes the LAST occurrence of _S, so if the input is 'STOP_01_S9_1_S12', the output would still be 12. The other solutions here would return 9_1_S12