How to use regular expression to generate short_link column in snowflake database

Question

How to use regex to replace all user_ids in column link? The user_id is always started with '11'

The query should look like this:

select link, xxxx as short_link from table

database: snowflake

| link                                                       | short link                       |
|------------------------------------------------------------|----------------------------------|
| /boba/users/11EAD36BE959A83709F187596AE5966BB/styles       | /boba/users/userid/styles        |
| /boba/search                                               | /boba/search                     |
| /boba/users/11EAD36BE959A84709F187596AE5966BB/posts        | /boba/users/userid/posts         |
| /boba/users/11EAD36BE959A85709F187596AE5966BB              | /boba/users/userid               |
| /boba/users/11EAD36BE959A8709F187596AE5966BB/notifications | /boba/users/userid/notifications |
| /boba/feeds/home                                           | /boba/feeds/home                 |
| /boba/search                                               | /boba/search                     |

Answer 1

Below code will work and provide expected output. For more information on REGEX_REPLACE please check this link.

https://docs.snowflake.com/en/sql-reference/functions/regexp_replace.html

with t as 
(
select '/boba/users/11EAD36BE959A83709F187596AE5966BB/styles' as link
union all
select '/boba/search' as link
union all
select '/boba/users/11EAD36BE959A84709F187596AE5966BB/posts' as link
union all
select '/boba/users/11EAD36BE959A85709F187596AE5966BB' as link
union all
select '/boba/users/11EAD36BE959A8709F187596AE5966BB/notifications' as link
union all
select '/boba/feeds/home' as link
union all
select '/boba/search' as link
)
select link,
REGEXP_REPLACE(link, '/11[A-Z0-9]{10,}', '/userid') AS short_link
from t;

Answer 2

We can use REGEXP_REPLACE here:

SELECT
    link,
    REGEXP_REPLACE(link, '/11[A-Z0-9]{10,}', '') AS short_link
FROM yourTable;

This regex replacement logic would target any path component starting with 11 followed by ten or more capital letters/digits as being a user ID.