'How can I remove characters in a string after a specific special character in snowflake sql?

I have a url column in my database. I want to remove all characters after ? .

select regexp_replace(page_url,'?(.*)', '') as clean_url from demo

But I'm getting the following error: Invalid regular expression: '?(.*)', no argument for repetition operator: ?

sqlsnowflake-cloud-data-platform


Solution 1:[1]

Using LEFT and CHARINDEX:

SELECT IFF(CHARINDEX('?', page_url)>0,
           LEFT(page_url, CHARINDEX('?', page_url)),
           page_url) AS clean_url 
FROM demo

Solution 2:[2]

? is a regular expression match token to a repeat prior token and thus to match it explicitly it needs to be escaped: \?

as per the doc's:

To escape meta-characters (as mandated by the POSIX standard). For example, to force the regular expression meta-characters * and ? to be treated as literals rather than as meta-characters, use \* and \?.

BUT if you are entering it via the SQL parser, it will need to be double escaped. thus need to be \\?

select
    page_url
    ,regexp_replace(page_url,'\\?(.*)', '') as clean_url 
from values
    ('https://example.com/page.html?parameter1')
    t(page_url);

gives:

PAGE_URL CLEAN_URL
https://example.com/page.html?parameter1 https://example.com/page.html

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Simeon Pilgrim

Related Questions