POSITION in Snowflake, also known as CHARINDEX in other databases, is a simple function that finds the location of a substring within a larger string.
Syntax:
POSITION(<substring>, <string> [, <start_position>])
- substring: the text you want to find
- string: the text you want to search within
- start_position: where to start searching from (optional, defaults to 1)
Note: POSITION returns the position of the first occurrence of the substring. If the substring isn't found, it returns 0. Position counting starts at 1, not 0.
Alternative Syntax: Snowflake also supports SQL standard syntax:
POSITION(<substring> IN <string>)
Example 1: Finding a character
SELECT POSITION('@', 'user@email.com');
-- Result: 5
Example 2: Finding a word
SELECT POSITION('flake' IN 'Snowflake');
-- Result: 5
Example 3: Substring not found
SELECT POSITION('xyz' IN 'Snowflake');
-- Result: 0
Pairing up with SUBSTR to make it dynamic
While POSITION is powerful on its own, it becomes even more useful when combined with SUBSTR. This allows you to dynamically extract substrings based on the location of specific characters or patterns, rather than hardcoding position numbers.
Example: Extracting everything after a delimiter
Let's say you have a table with email addresses and want to extract just the domain:
SELECT
email,
SUBSTR(email, POSITION('@', email) + 1) AS domain
FROM emails;
Result:
email | domain
-------------------------|-------------
john.doe@company.com | company.com
jane.smith@tech.io | tech.io
bob.jones@startup.co | startup.co
Example: Extracting everything before a delimiter
Now let's extract just the username (everything before the @):
SELECT
email,
SUBSTR(email, 1, POSITION('@', email) - 1) AS username
FROM emails;
Result:
email | username
-------------------------|------------
john.doe@company.com | john.doe
jane.smith@tech.io | jane.smith
bob.jones@startup.co | bob.jones
By combining POSITION with SUBSTR, you can handle real-world data that doesn't follow a fixed format, making your queries more flexible and maintainable.