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.
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.
right ( "stop_name", length("stop_name")-regexp_match("stop_name" ,'(_S\\d+)')-1)
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
