6

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.

Babel
  • 71,072
  • 14
  • 78
  • 208
Arthur
  • 893
  • 6
  • 20

3 Answers3

6

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.

Erik
  • 16,269
  • 1
  • 24
  • 43
5
right ( "stop_name", length("stop_name")-regexp_match("stop_name" ,'(_S\\d+)')-1)

enter image description here

Babel
  • 71,072
  • 14
  • 78
  • 208
5

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

JGH
  • 41,794
  • 3
  • 43
  • 89