GET/Remove the last character from a string in SQL Server

In some of cases we need remove last character from the string.In this tip we will see how to get and remove last character in string.

Sample string: 'DotNetMirror,'

Now we will remove , from the string.

Get Last Char in string:

Declare @STRING varchar(100)

set @STRING='DotNetMirror,'

select RIGHT(@String, 1)

Remove the last char(comma) in string :

IF(LEN(@String)> 0 AND RIGHT(@String, 1) = ',')

BEGIN

select substring(@String, 1, (len(@String) - 1)) --or

SET @String = LEFT(@String, LEN(@String) - 1)

END

select @String

 


 

Iterating/Looping through rows without using cursors in Sql server

In this snippet we will see how to  iterating the table rows data with out using cursor in SQL server.

 

set nocount on go declare @tempEmpTable table(EmpID int , EmpName nvarchar(100)) declare  @varEmpID int, @varEmpName nvarchar(100) insert into @tempEmpTable select 1, '1st Employee' union all select 2, '2nd Employee' union all select 3, '3rd Employee' union all select 4, '4th Employee' union all select 5, '5th Employee' union all select 6, '6th Employee' union all select 7, '7th Employee' union all select 8, '8th Employee' union all select 9, '9th Employee' select top 1 @varEmpID = EmpID, @varEmpName = EmpName  from @tempEmpTable   while @varEmpID is not null begin                 print @varEmpName                 delete  @tempEmpTable   where @varEmpID = EmpID                 set @varEmpID = null                          select top 1 @varEmpID = EmpID, @varEmpName = EmpName  from @tempEmpTable   end

Output: 1st Employee 2nd Employee 3rd Employee 4th Employee 5th Employee 6th Employee 7th Employee 8th Employee 9th Employee

Rollback Transaction for table variable in sql server

Table variable is special kind of data type which will store result set and the data will returned for processing. Table variable can be used in store procedures and functions.

Table variables have limited scope and data will be stored in temporary storage so they are not affected with transaction roll backs.

In the below script we will see practically how rollback behaves for table variable.

DECLARE @student AS Table (id INT,Name VARCHAR(50) NOT NULL)   BEGIN TRY     BEGIN TRANSACTION            INSERT INTO @student Values(1,'Ram')     INSERT INTO @student Values(2,'Rahim')     INSERT INTO @student Values(3,NULL)     INSERT INTO @student Values(4,'Rohan')     COMMIT TRANSACTION     SELECT *,Name FROM @student -- will not return data due to error at id=3 END TRY BEGIN CATCH     ROLLBACK TRANSACTION     SELECT *,id FROM @student END CATCH

Output:

 In summary, we can say that Rollback Transaction does not work for table variables.

SET IDENTITY_INSERT for Table Variable in SQL Server

SET IDENTITY_INSERT - Allows to insert explicit values to identity column of the table.

SET IDENTITY_INSERT ON/OFF works for talbes and temporary table.
we can not use for table variables.

If we run a sample query below

DECLARE @student TABLE (ID INT IDENTITY,Name VARCHAR(50))   INSERT INTO @student(Name) Values('A') INSERT INTO @student(Name) Values('B') INSERT INTO @student(Name) Values('C') INSERT INTO @student(Name) Values('D')   DELETE FROM @student WHERE ID = 3   SET IDENTITY_INSERT @student ON   INSERT INTO @student(ID,Name) Values(2,'E')

we will get error as

Msg 102, Level 15, State 1, Line 10

Incorrect syntax near '@student'

Difference between count(*) and count(column_name)

We might assume that count(*) and count(column_name) will return same result count. But NO, in case of column holds any null values.

Count (*) – returns all values (including nulls and duplicates)

Count (Column_Name) – return all Non-NULL values

In the below script we will see how it works. So that it will be easy for us to understand.

create table #tempTable(Name char(1)) insert into #tempTable values('A') insert into #tempTable values('B') insert into #tempTable values(Null) insert into #tempTable values('C') insert into #tempTable values(Null) insert into #tempTable values('C') select COUNT(*) from #tempTable select COUNT(Name) from #tempTable drop table #tempTable

Output: 6 and 4

The table #temptable has total 6 rows. Count(*) retruns all the rows including null/duplicates but where as count(name) returns only 4 rows which includes duplicates(‘C’) but not null values.

If you want to remove duplicates from count(Name) use COUNT(distinct Name).

select COUNT(distinct Name) from #tempTable –returns 3.

Replace function in SQL Server with examples

In this article we will discuss the definition and syntax of replace function along with examples like simple usage of replace, string found multiple times, no string found, passing INT data as string to replace, replace with update statement and where condition, replacing single quote with double quote and using of nested replace function.

"REPLACE is a SQL server function which replaces the specified string with another string."

Syntax:

REPLACE(string1,string2,string3)

string1 - string to be searched

string2 - string to be found

string3 - string to be replaced.

string1, string2 or string3 can be of type character or binary data type.

Return Type:

·         nvarchar -if one of the input arguments is of the nvarchar data type

·         varchar – argument datatype as other than nvarchar data type

·         NULL - if any one of the arguments is NULL.

Let’s understand with simple example

SELECT REPLACE('DotNetMirror','dot','.')

Output - .NetMirror

Explanation: The searched string “dot” is found once in  'DotNetMirror’ so it will replace dot string with . and returns “.NetMirror ”

String found multiple times:

If string2 found multiple times in string1 then it will replaces all the occurrences which are matched with string3 in string1.

SELECT REPLACE('DotNetMirror DotNetMirror','dot','.')

Output - .NetMirror .NetMirror

Explanation: The searched string “dot” is found 2 times in  'DotNetMirror DotNetMirror' so it will replace dot string with . and returns “.NetMirror .NetMirror”

No String found:

If none of string found in string1 then it will return the orginal string i.e. string1 

SELECT REPLACE('DotNetMirror','reflects','.')

Output: -  DotNetMirror

Explanation: Because “reflects”string is not found in DotNetMirror so it will not replace any content with .(dot) and returns original value

 Passing INT value to be searched in string:

We can pass INT values in string2 and string3 that will also replaces if the string matches. 

SELECT REPLACE('DotNetMirror 456','456','123')

Output: -  DotNetMirror 123

Explanation:  The searched string “456” is found once in  'DotNetMirror 456’ so it will replace 456 string with 123 and returns DotNetMirror 123”

Replace with update statement

In some scenarios we need to replace a string in a table column data so we can use below query.

UPDATE Student SET FirstName=REPLACE(FirstName, 'Kumar', 'Kumar sir')

Output: - replaces first name column data with ‘kumar ji’ if any of string contrains kumar in column data.

Replace with update statement and where condition:

The above statement replaces all the matched occurrences in the column data in order to update for particular columns we can replace function in update statement with where condition.

E.g :

UPDATE Student SET FirstName=replace(FirstName, 'Kumar', 'Kumar sir') WHERE Country='India'

Output: - replaces first name column data with ‘kumar ji’ if any of string contrains kumar in FirstName column data for students those belongs to country India.

Replace single-quote (') with double-quote ("):

In order to get a single quote in a string we need to give two single quotes.

select replace('''DotNETMirror''', '''', '"')

Output: "DotNETMirror"

Using nested replace:

select replace(REPLACE('DotNetMirror','dot','.'),'.','Dot')

Output: DotNetMirror

Explanation: From inner replace method dot is replaced with .(dot)  which returns .NetMirror and again from outer replace function .(dot) is replaced with Dot so it returns the original value “DotNetMirror”

Difference between Primary and unique key in SQL Server

Primary key vs. unique key

In this article we will see the difference between primary key and unique key with practical scenarios.

 

Primary Key	Unique Key
A table can have only one primary key	A table can have any number of unique keys
Does not allow null value so implicitly enforces not NULL constraint	Allows only one NULL value
Creates clustered index on the column	Creates non-clustered index on the columns

In the below script we will see the above points.

--table Create table PrimaryVsUniqueKey( id int constraint PK primary key, Col1 varchar(50) CONSTRAINT UniqueKey1 unique,Col2 varchar(50) constraint UniqueKey2 unique) --insert statements Insert into PrimaryVsUniqueKey values(1,'Ram','Laxman') --success Insert into PrimaryVsUniqueKey values(1,'Ajay','Vijay') --error as Violation of PRIMARY KEY constraint 'PK'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey' The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,'Ram','Vijay') --error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'.The statement has been terminated. Insert into PrimaryVsUniqueKey values(Null,'John','Cena') -- error as Cannot insert the value NULL into column 'id', table 'Practise.dbo.PrimaryVsUniqueKey'; column does not allow nulls. INSERT fails. The statement has been terminated. Insert into PrimaryVsUniqueKey values(2,Null,Null) -- success Insert into PrimaryVsUniqueKey values(3,Null,'Test') -- Error as Violation of UNIQUE KEY constraint 'UniqueKey1'. Cannot insert duplicate key in object 'dbo.PrimaryVsUniqueKey'. select * from PrimaryVsUniqueKey

Output: