In this blog, I will discuss about “How to remove trailing spaces and zeros from a string without using any trim function”.I have written a procedure for the same. In one of my blog where I have shared the code for removing the leading zeros and spaces, here as well, code is almost the same. CREATE PROCEDURE [dbo].[RmTrailingZerosSpcs] (@String VARCHAR(500) )asBEGINDECLARE @output VARCHAR(100) = ”;DECLARE @prev CHAR(1) = ”;DECLARE @c CHAR(1) = ”;DECLARE @i int = 1;DECLARE @len int;DECLARE @len_zeros int =0;declare @reversestring varchar(200)select @reversestring= reverse(@String)SET @len = len(@reversestring);WHILE(@i <=…
Category: DATABASE
Remove Leading Zeros and Spaces In SQL-SERVER
In this Blog, I will discuss about “How to remove Leading Zeros and Spaces from a value in SQL server”. I have written procedure for it . See the below code . CREATE PROCEDURE [dbo].[RmLeadZerosSpcs] (@String VARCHAR(500) )asBEGINDECLARE @output VARCHAR(100) = ”;DECLARE @prev CHAR(1) = ”;DECLARE @c CHAR(1) = ”;DECLARE @i int = 1;DECLARE @len int;DECLARE @len_zeros int =0;SET @len = len(@String);WHILE(@i <= @len) begin SET @c = SUBSTRING(@String, @i, 1); IF @c=’0′ or @c=’ ‘ BEGIN SET @len_zeros=len(@c)+@i SET @output = substring(@String,@len_zeros, @len); SET @i = @i + 1;…
ORACLE INTERVIEW QUESTIONS
In this blog, I gathered few Interview Questions asked on Oracle Database. It will be very useful for 2-4 years of Experience IT Professionals.1. When we should use Materialized view and non materialized view.2. Difference between Ref cursor and sys ref cursor.3. What are the different types of TRIGGERS.4. Difference between SUBSTRING and INSTRING function.5. If a query is taking more time to display results, What’s first step will you perform to check this issue.6. Exceptional handling in Oracle procedure.7. Name PSEUDO columns in Oracle.8. Difference between Schema and User…
Check existence and non-existence of columns in a table
In this Bog, We will prepare a query which will tell us which tables has one specific column but another column which is also important is not present.QUERY-1select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS c where COLUMN_NAME=’column1′ and not exists ( select 1 from INFORMATION_SCHEMA.columns cc where cc.TABLE_NAME=c.TABLE_NAMEand cc.COLUMN_NAME=’column2′) The above Query will list all tables in which column1 is present and column2 is not present. QUERY-2select distinct TABLE_NAME from INFORMATION_SCHEMA.COLUMNS c where COLUMN_NAME=’column1′ and exists ( select 1 from INFORMATION_SCHEMA.columns cc where cc.TABLE_NAME=c.TABLE_NAMEand cc.COLUMN_NAME=’column2′) This Query will list all tables in…
get all Values separated by delimiter in sql
In this blog, I will discuss about “How to get all values separated by delimiter in SQL”. In one of my previous blog, where I have explained about positions of all delimiters present in a string. We will use same logic here as well. For example I have string with value abc@gmail.com;xyz@gmail.com;123@gmail,comIn the above string, I have three email ids which are separated by delimiter ; . I need these values in below format abc@gmail.comxyz@gmail.com123@gmail,com Use the below code to fetch the data in above format . declare @var1 varchar(100)…
ROLLBACK IN SQL SERVER
Lets discuss about the rollback code or syntax in the SQL-Server. In SQL Server Settings, we generally set auto-commit on. I have worked on many projects where database is SQL-Server and most of them(team mates) were not aware of Rollback syntax in SQL-Server. In oracle, auto-commit is generally set to off. So, we always press on commit button in SQL Developer or any other tool or we use syntax “Commit;” to commit the uncommitted transactions like insert/update. In SQL Server, If you want to explicitly want to commit the transactions…
GET HIERARCHICAL VALUES PRESENT IN SAME COLUMN OF A TABLE
Lets discuss about the parent and child relationship present in the same column of a table. That means both exists in the same column and there is always a identifier which says whether a record belongs to parent value or it belongs to child value. See the below data .As it is clearly seen from the data, parent and child record can be easily identified from column c1. Here , we need to apply self join to get parent and child values in the same row. See the below Query.…
Data validation across environments using HASH
Today i will discuss about data validation across environments using HASH function. There are times we need to validate the data of same table in different environments which is very monotonous and time consuming task while doing manually. Its better to automate the process. Here , I am considering the three environments Hive,IBM DB2,Snowflake Database.HIVE:md5(concatenated_columns)SNOWFLAKE:MD5_HEX (concatenated_columns)IBM DB2:HEX(HASH_MD5( concatenated_columns) Here, you need to take care of below things.1. Hexadecimal Output may in uppercase or lowercase, so handle with UPPER or LOWER function across environments.2. You need to take care of nulls…
Count the number of nulls using left join
Today, I will discuss about How to calculate the number of nulls that gets populated with left join or %age of matched data or count of not nulls using left join. CASE 1:Calculate the number of nulls that gets populated from right hand table using left join.Assume , I have two employee table EMP_TBL1 and EMP_TBL2. See the data for the same. EMP_TBL1 EMP_TBL2 below is the code used to get number of nulls that comes from right side table using left join. select count(*)-count(y_empid) from ( select x.emp_id ,y.emp_id…
Internal and External tables in Hive
Today, I will present types of tables that can be created in hive in a different way. I created three tables in hive.create table empdtls (emp_id int ); { This is called Internal or Managed table} create external table empdtls_ext (emp_id int ); { This is External table } create external table empdtls_ext_v2 ( emp_id int ) location ‘/user/demo/hivetesting/’ { This is also called External table} Now, we will check the TABLE_TYPE of all above tables using command DESCRIBE FORMATTED table_name Internal Or Managed Table/user/hive/warehouse/ is the location where all…