Snowflake SQL Field Function-CodePudding

I am running this query in Snowflake SQL

SELECT field('q', 's', 'q', 'l');

However I get this error:

SQL compilation error: Unknown function FIELD

Anyway I can find the position of something in an "IN" statement?

Ideally in a statement such as:

SELECT position_of_letter_in_in_statement, letter
from my_table a
where letter in ( 'q', 's', 'q', 'l');

with the output being as so:

position_of_letter_in_in_statement | letter
1                                  | 'q'
2                                  | 's'
3                                  | 'q'
4                                  | 'l'

CodePudding user response：

I don't understand the business requirement but here are two solutions.

1- Concat values in IN statement and use POSITION:

select position(letter, 'abcd'),  
letter 
from my_table  
where letter in ( 'a','b','c','d');

2- Use an ARRAY:

select ARRAY_POSITION( letter::variant, ARRAY_CONSTRUCT( 'a','b','c','d'))   1 position_of_letter_in_in_statement,  
letter 
from my_table 
where position_of_letter_in_in_statement is NOT NULL;