Extracting words from a string after a specific character

[kmardinian]

Hi, I have a variable called "comment" that contains a string of words that are separated by '~'

example:

comment

text text text text ~ text text

text text text text text ~ text text text ~text text 

I would like to extract the text after the first ~ without losing the text behind the second or third or fourth ~ etc.

I tried using this code:

cm=scan(comment,1,"~");

But it only extracts the text after the first ~ and then stops once it reaches the second ~

Any help is much appreciated!

[Reeza · Accepted solution]

Use INDEX() to find the first tilda and then use that number in SUBSTR().
Double check the order of t

cm = substr(comment, index(comment, '~') +1);

[kmardinian]

Hi Reeza,

Thank you for the quick response. Out of curiosity, what does the +1 signify?

[Reeza]

INDEX would find the ~, so if you substring from that it includes the ~, +1 gives you the position after the first ~.