Isolate string starting before specific string

user_7920004 0
Viewed 89

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)
snowflake-cloud-data-platform sql regex
3 Answers

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'))
user_11440350

To extract number with ML as suffix use

select regexp_substr(field, '[0-9]+ML')

EXPLANATION

NODE                     EXPLANATION
--------------------------------------------------------------------------------
  [0-9]+                   any character of: '0' to '9' (1 or more
                           times (matching the most amount possible))
--------------------------------------------------------------------------------
  ML                       'ML'
user_113298900

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.
user_38329700
Related