Isolate string starting before specific string

Question

My string is as follows:

'NAME NAME NAME 400ML NAME CODE'

I need to identify ML, go left to catch all digits before that and stop on first space to get:

400ML

Current code I have:

 SUBSTR(FIELD,CHARINDEX('ML',FIELD), 2)

Answer 1

I suggest using

regexp_substr(field, '\\d+\\s*ML\\b')

This regex will make sure the ML is matched as a whole word, and if there are any whitespaces between a number and ML, they will also be matched.

See the regex demo.

Regex details

• \d+ - 1 or more digits
• \s* - 0 or more whitespaces
• ML - a string ML
• \b - a word boundary.

Answer 2

You can use regexp_substr():

select regexp_substr(field, '[^ ]+ML')

Or for specifically alphanumeric characters:

select regexp_substr(field, '[a-zA-Z0-9]+ML')

If Snowflake is not greedy (which seems unlikely but is possible), then you can do:

select trim(regexp_substr(' ' || field, ' [a-zA-Z0-9]*ML'))