CODEWITHSUNDEEP
sqlregexsnowflake-cloud-data-platform
marcin2x4
marcin2x4

Reputation: 1429

Isolate string starting before specific string

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)

Upvotes: 3

Views: 110

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 626689

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.

Upvotes: 2

Ryszard Czech
Ryszard Czech

Reputation: 18611

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'

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269463

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

Upvotes: 2

Related Questions