0

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

user6557846
  • 1
  • 1
  • 1
  • 2
  • 1
    Possible duplicate of [IndexOf function in T-SQL](http://stackoverflow.com/questions/1869465/indexof-function-in-t-sql) – Conduit Jul 06 '16 at 19:55

1 Answers1

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.

Bruce David Wilner
  • 463
  • 2
  • 4
  • I don't know the length of the number or the actual number either. I know i would need two separate functions to find each number which is fine I am just not sure how. – user6557846 Jul 07 '16 at 13:46