Tech-Discussion

       SPLIT_TO_TABLE Sometimes we get a string with commas separated and we need to convert it into rows as below: Suppose you receive a string in this format ' 1,2,3,4,5 ' And you want to return like this. MS-SQL Server   You can achieve this by using multiple ways.  In the recent version of MS SQL Server, they came up with a new String Function  STRING_SPLIT . In  Snowflake ,  we can use a similar function  SPLIT_TO_TABLE  as  STRING_SPLIT.   For more details, you can refer to the following  SPLIT_TO_TABLE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>

        iLike ANY If you have a dataset as below and want to make a comparison to match a string against data with a wild card like 'o%' or 'f%': Expected Result: MS-SQL Server   You can do this using a like clause along with OR as below:  In  Snowflake ,  we can go with a similar approach, however, w e can use the iLike and flexibility of ANY. For more details, you can refer to the following  iLike_Any .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

       NTH_VALUE If you have a dataset and you want to calculate the third highest value in each group as below: + ---------+---------+ | COLUMN1 | COLUMN2 | | ---------+---------+ |       1 |       10 | |       1 |       11 | |       1 |       12 | |       1 |       20 | |       1 |       21 | |       1 |       22 | |       2 |       30 | |       2 |       31 | |       2 |       32 | |       2 |       40 | |       2 |       41 | |       2 |       42 | + ---------+---------+ Expected Result: + ---------+---------+ | COLUMN1 | COLUMN2 | | ---------+---------+ |       1 | ...

      INITCAP Sometimes we need to provide strings/lines in a specific format as per the client's request. Suppose you want to capitalize the first letter of each word in the string and all other letters appear in lowercase.  It needs special treatment, known as  InitCap (Initial Capitalisation). MS-SQL Server   You can only achieve this differently. You need to write a function or couple of lines of code as below:  In  Snowflake ,  we don't need to write so many lines of code . We can use the inbuilt function  INITCAP. Also, this function gives you the flexibility to provide a delimiter  that INITCAP uses as a separators for words in the input expression.  In the below example, 'q' is used as the delimiter. For more details, you can refer to the following  INITCAP .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>  

      Else IF Many times you have to compare the equation and then make the decision. Suppose the value is greater than 100 then it should print " The number is large ". if it is less than 10 then it should print " The number is small " and for all the remaining it should print " The number is medium ". MS-SQL Server   You can achieve this by using the  IF ELSE statement.  In  Snowflake ,  we can use a similar IF Else but it has a variant ELSEIF as below .   For more details, you can refer to the following  ELSEIF .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>

      String Aggregate Sometimes we need to provide the column data into comma-separated values as below: Suppose you have values like this in a table And you want to return like this. MS-SQL Server   You can achieve this by using multiple ways.  In the recent version of MS SQL Server, they came up with a new String Function STRING_AGG . In  Snowflake ,  we can use a similar function  LISTAGG  as STRING_AGG.   For more details, you can refer to the following  LISTAGG .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>

     DECODE If you want to return a value based on some condition(s). Suppose you have values like this in a table And you want to return like this. MS-SQL Server   You can achieve this by using the  CASE Statement .  In  Snowflake ,  we can use a similar syntax as above; h owever, Snowflake provides an alternative  DECODE .   For more details, you can refer to the following  DECODE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>