sql get a substring after a certain word in the string

Question

How can I get part of a string after a specific word in the string? for example: Section 123 Block 123 I need the number 123 after Section and Block

Answer 1

First of all, if 123 occurs TWICE in the string, you can't get "the number" after TWO things. "The number" means ONE of them.

You can't return TWO values from a function.

Aside from these obvious gaffes, I'll assume you want the text that follows the FIRST (if it's there) occurrence of the pattern in the string.

SUBSTR( columnName, LOCATE( '123', columnName ) + 3 )

The reason for the "+ 3" is that we want to start searching three characters past the beginning of the '123'. A more professional programmer might code it this way:

SUBSTR( columnName, LOCATE( '123', columnName ) + LENGTH( '123' ) )

(It isn't really necessary when it's obvious that the length of '123' is three, but if you were looking for, say, Massachusetts, you might want to specify 13 because people can't instantly spot the length of 'Massachusetts'.)

Bear in mind that, if the substring doesn't occur, this will fail.