Snowflake - checking for pattern and returning substring within that pattern

Question

I have a table with multiple columns that I have to look through to check for this value: '[m=' and return the numbers within that list.

For example lets say I have this in the 'name' column xyzzy [m=123] I want to return 123. I was trying the below query but its not working because I keep seeing the following error:

SQL compilation error: syntax error line 3 at position 4 unexpected 'WHEN'. syntax error line 3 at position 34 unexpected ')'.

I also think SPLIT_PART isn't going to work because then if I have xyzzy [m=123] it will return 123] and I don't want the closing bracket included

case 
    WHEN REGEXP_SUBSTR(name, '[m=') THEN SPLIT_PART(name, '[m=', )
    WHEN REGEXP_SUBSTR(name2, '[m=') THEN SPLIT_PART(name2, '[m=', )
    WHEN REGEXP_SUBSTR(name3, '[m=') THEN SPLIT_PART(name3, '[m=', )
    WHEN REGEXP_SUBSTR(name4, '[m=') THEN SPLIT_PART(name4, '[m=', )
    WHEN REGEXP_SUBSTR(name5, '[m=') THEN SPLIT_PART(name5, '[m=', )
    WHEN REGEXP_SUBSTR(name6, '[m=') THEN SPLIT_PART(name6, '[m=', )
else null;


+------+--------+---------+------------+------+----------------+---------------+
| name         |   name2 | name3 |   name3     | name5       | name6            |
+------+--------+---------+------------+------+----------------+---------------+
| xyzzy [m=123]  | MyISAM |  10 | Fixed       |    0        | my comment      | 
+------+--------+---------+------------+------+----------------+---------------+
| rts3         | MyISAM  |    1 | test [m=122]  |    4        | my comment     |   
+------+--------+---------+------------+------+----------------+---------------+
| rddts3       | MyISAM  |    1  | dm32dfe     |    4        | comment [m=177]  |      
+------+--------+---------+------------+------+----------------+---------------+

*** EDIT: I've tried adjusting my query to look like this:

Select *,
CASE
 WHEN REGEXP_SUBSTR(NAME, '(?:\[m=)') THEN REGEXP_SUBSTR(NAME, '[[]m=([0-9]+)'),
 WHEN REGEXP_SUBSTR(NAME2, '(?:\[m=)') THEN REGEXP_SUBSTR(NAME2, '[[]m=([0-9]+)'),
ELSE null
END
from my_table

but now I'm seeing this error: SQL compilation error: error line 2 at position 0 Invalid argument types for function 'IFF': (VARCHAR(16777216), VARCHAR(16777216), NULL)

Gordon Linoff · Accepted Answer · 2021-07-26 19:13:12Z

1

You can just use regexp_substr() with a capture group:

REGEXP_SUBSTR(name, '[[]m=([0-9]+)', 1, 0, 'e')

I'm not sure what your case expression is for. If you want the first match in a bunch of names, you can use:

COALESCE(REGEXP_SUBSTR(name, '[[]m=([0-9]+)', 1, 0, 'e'),
         REGEXP_SUBSTR(name2, '[[]m=([0-9]+)', 1, 0, 'e'),
         . . .
        )

answered Jul 26, 2021 at 19:13

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

KristiLuna Over a year ago

Thanks for responding! @Gordon Linoff I tried this based on what you mentioned: Select *, case WHEN REGEXP_SUBSTR(NAME, '(?:[m=)') THEN REGEXP_SUBSTR(NAME, '[[]m=([0-9]+)') else null end from my_table; but I'm seeing the error I mentioned in my question that I've added as an 'edit'

Gordon Linoff Over a year ago

@KristiLuna . . . Run the code without the case. There is no case expression in this answer.

Collectives™ on Stack Overflow

Snowflake - checking for pattern and returning substring within that pattern

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related