'sql alias to function for reuse in select

im trying to make the query more efficient, is there a way to use an alias in order to call regexp_substr only once?

this is the sql query:

SELECT *,
   (SUBSTRING(REGEXP_SUBSTR(A, '(://([a-zA-Z0-9]+):)', 1, 1, 'c'), 4, LENGTH(REGEXP_SUBSTR(A, '(://([a-zA-Z0-9]+):)', 1, 1, 'c')) - 4)) as custom
FROM table
sql


Solution 1:[1]

No need for a second regexp_substr, simply use 2 argument SUBSTRING. (I.e. substring from position 4 to the end):

SELECT *,
       SUBSTRING(REGEXP_SUBSTR(A, '(://([a-zA-Z0-9]+):)', 1, 1, 'c'), 4) as custom
FROM table

https://docs.snowflake.com/en/sql-reference/functions/substr.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 jarlh

Related Questions