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 >>>      

    TRANSLATE If there are unwanted characters in the database and you want to replace or remove those unwanted characters.  If you see here, the actual string is " SQL Coding is Fun " but somehow there are unwanted and the string is saved as " SQL x Codi y ng is z Fun ".  We need to remove additional characters x,y & z. MS-SQL Server   You can achieve this by using  REPLACE . But you need to take multiple iterations. In  Snowflake ,  we can use a similar syntax as above; h owever, Snowflake provides an alternative  TRANSLATE .     By using this we can achieve a single iteration. For more details, you can refer to the following  TRANSLATE .  Yogesh Shinde LinkedInProfile <<< Back   Next >>>      

    CONCAT Suppose you have a table and it is storing department ID as below.  +-------------+------------+------------+---------------+ | EMPLOYEE_ID | LAST_NAME | FIRST_NAME | DEPARTMENT_ID | |-------------+------------+------------+---------------| | 101 | Montgomery | Pat | 1 | | 102 | Levine | Terry | 2 | | 103 | Comstock | Dana | 2 | +-------------+------------+------------+---------------+ As per requirement, you want to display the department ID as Dept-1. +-------------+------------+------------+---------------+ | EMPLOYEE_ID | LAST_NAME | FIRST_NAME | DEPARTMENT_ID | |-------------+------------+------------+---------------| | 101 | Montgomery | Pat | DEPT-1 | | 102 | Levine | Terry | DEPT-2 | | 103 | Comstock | Dana | DEPT-2 | +-------------+------------+------------+---------------+ MS-SQL Server ...

    Lateral Join Suppose you have two tables as below: And you need to pull the data from both tables as below: MS-SQL Server   There are multiple approaches to achieve this. The common one is to use Inner Join. Another alternate is CROSS APPLY as below. In  Snowflake ,  we can not use CROSS APPLY as it does not support it. However, it provides an equivalent of it i.e.  LATERAL JOIN.   A lateral join behaves more like a correlated sub-query as below: For more details, you can refer to the following  LATERALJOIN .  Note: However MS-SQL Server provides you OUTER APPLY additionally and Snowflake does not have an equivalent of it, you may need to use LEFT JOIN. Yogesh Shinde LinkedInProfile <<< Back   Next >>>     

    QUALIFY You might be using the Windows function for different purposes and need to filter this result. MS-SQL Server   If you want to filter in this situation then you need to use an outer query to filter it out . In  Snowflake ,  we can use a similar code as above or we can make use of QUALIFY to avoid an additional outer query. Also, you can use the Windows function to Qualify as below. For more details, you can refer to the following  QUALIFY . Yogesh Shinde LinkedInProfile <<< Back   Next >>>