Remove trailing spaces and zero from a string in SQL-Server

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

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

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

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…

get position of all delimiter values in a string

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

Query to get all records not matching in all tables.

Today, I will discuss about the scenario/Problem which i faced in current project. Problem Statement : You have two Employee tables having data mentioned below. Emp-1 EmpId EmpName 1 Shivani 2 Gaurav 3 Radhe 4 Rahul 5 Anil Emp-2 EmpId EmpName 1 Shivani 6 Ashok 3 Radhe 7 Vikram 4 Rahul Output : EmpId EmpName 2 Gaurav 5 Anil 6 Ashok 7 Vikram Solution : Here, you need to use except or minus based on the database you are using and union clause. See the below query for the same.…