Today, I will explain about “How to get position and values separated by delimiter in a string”. Delimiter may be comma,pipe,colon etc. STEP1 : Get position of all delimiters in a string. I am explaining with SQL-server as Database. ;with T as ( select 0 as row_id, charindex(‘,’, @string) pos union all select pos + 1, charindex(‘,’, @string, pos + 1) from T where pos > 0)select * from T For example , consider a string “Harsimran,Kaur,91-123-456-7890” . So, it has first name and last name and dummy mobile number.…
Month: August 2020
Check duplicate record in Hive
Today, I will discuss about ” How to automate the process where in you can check entire row duplicate record in hive”. As I have mentioned in all Automation blogs, I will share the pseudo code.STEP1: In hive , use “desc table_name” , this command will give you column names along with datatype and data length. Store the output of this command in a file , say HIVE_TABLE_DDL.txt STEP2 : Read the file HIVE_TABLE_DDL.txt using “cat” command. cat HIVE_TABLE_DDL.txt | awk ‘{print $1}’ ORS=’,’ | sed ‘s/,$//’ * awk'{print $1}’…
equivalent of sum of columns in SAS
Today, I will discuss about “How to design SAS code which is equivalent of sum of columns in database. Consider the below employee details. In order to get sum of Year-2019 and Year-2020, we need to use add (+) operator to achieve the same. See the below Query and data for the same. Here, i applied ,normal addition, sum using ISNULL function, difference using absolute(ABS) function. In order to achieve the same in SAS . Create the same Datasets . See the below Screen Shot for the same. Now, create…