CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Hannah
Hannah

Reputation: 85

snowflake substring with pattern

A column in sf table has values like

test/SalesID
test2/SalesIDaccount
mytempSalesID
1234SalesIDnum
...

I want to substring this column to show only

SalesID
SalesIDaccount
SalesID
SalesIDnum

so remove everything before SalesID and keep whatever left. How to approach it?

Upvotes: 1

Views: 192

Answers (2)

snowflake_user
snowflake_user

Reputation: 118

Use regexp_substr

select regexp_substr(col, '.*(SalesID.*)', 1, 1, 'e', 1) as "RESULT";

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You can use split_part(). To get the last component:

select split_part(col, '/', -1)

To get the second component:

select split_part(col, '/', 2)

It is not clear if you want the last, second, or if it doesn't matter.

Upvotes: 0

Related Questions