CODEWITHSUNDEEP
sqlregexsnowflake-cloud-data-platform
Optional
Optional

Reputation: 105

Snowflake regexp return zero rows

I've got a strange scenario in Snowflake that i am not too sure how to solve. I have some data in a column that looks like this -

ID, Names
1, Google | Bing | BAU | Sale
2, BAU | Sale | Bing
3, Google | Bing

I am attempting to use REGEXP to pull out anyrows that match my pattern like so -

where Name regexp ('Google|Bing')

Which i was hoping would return all 3 rows - at this stage i get zero.

Appreciate any help you guys can offer

Upvotes: 0

Views: 112

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25903

SELECT column1 
    ,regexp_count(column1, 'Google|Bing')
FROM VALUES
('Google | Bing | BAU | Sale'),
('BAU | Sale | Bing'),
('Google | Bing');

gives:

COLUMN1 REGEXP_COUNT(COLUMN1, 'GOOGLE|BING')
Google | Bing | BAU | Sale 2
BAU | Sale | Bing 1
Google | Bing 2

so the pattern matches something...

SELECT column1 
FROM VALUES
    ('Google | Bing | BAU | Sale'),
    ('BAU | Sale | Bing'),
    ('Google | Bing')
WHERE column1 regexp '.*(Google|Bing).*';

works

COLUMN1
Google | Bing | BAU | Sale
BAU | Sale | Bing
Google | Bing

because:

The function implicitly anchors a pattern at both ends (i.e. '' automatically becomes '^$', and 'ABC' automatically becomes '^ABC$'). To match any string starting with ABC, the pattern would be 'ABC.*'.

Upvotes: 1

Related Questions