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.

  • Please provide examples for what you tried and how they don't suit your needs.
    – Erik
    Commented Sep 9, 2021 at 13:43

3 Answers 3



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.

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

enter image description here


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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.