How to Backup and Restore of SQL Server database through VB.NET

This article describes how to back up and restore a SQL Server database using vb.net. The following code example requires a windows form with some combobox and textbox controls (see below picture.) Continue reading “How to Backup and Restore of SQL Server database through VB.NET”

LEN() and REPLICATE() functions in SQL Server

 

LEN() function is used to return the number of characters of a given character string. It allows us to determine the length of string correctly in characters, and whether or not a unicode string is being used.

REPLICATE() function returns a character string repeated a specified number of times.It can be useful for formatting output from a query. for example you can print ‘-‘ character many times with the help of PRINT statement.

Example:

Suppose we have a table ‘tbl_employee’

Table name: tbl_Employee

Employee IdEmpNameSalaryDepartment
1Ankur1200Sales
2David3400Sales
3John1500HR
4James1200Production
5Mohan4500Production
6Ram2300HR

from the following query we can get length of Employee name:

SELECT LEN(EmpName) AS NameOFLength, EmpName
FROM tbl_employee
WHERE Department= ‘HR’;

Output Will be:

NameOfLengthEmpName
4John
3Ram

Example of Replicate() function:

The following example replicates a ‘0’ character four times leading with three character of EmpName and trailing with EmployeeId.

SELECT [Empname], LEFT(UPPER([Empname]),3) + ‘-‘ + REPLICATE(‘0’, 4) +
Convert(CHAR(1),[Employee id]) AS ‘EmpCode’
FROM tbl_employee

Output Will be:

EmpNameEmpCode
AnkurANK-00001
DavidDAV-00002
JohnJOH-00003
JamesJAM-00004
MohanMOH-00005
RamRAM-00006

Difference between Stored Procedure and Function

Here i am presenting an exhaustive list of difference between Stored Procedures and Functions in Database.

1. Functions can be called from procedure where as procedures cannot be called from function.
2. Procedures cannot be utilized in a select statement whereas function can be embedded in a select statement.
3. Function must return one and only one value where as Procedure can return any number of values (0 to n).
4. Function can only have input parameters only where as Procedure can have input as well as output parameters.
5. Function can have only select statement in it where as a Procedure can have select as well as DML statement in it.
6. Procedure can use try-catch block to handle exception whereas try-catch block cannot be used in a function.
7. Function can be used in an assignment operation whereas Procedure cannot be part of assignment operation.

RTRIM() and LTRIM() functions in SQL Server

RTRIM() function removes any trailing blanks from a string or column. And LTRIM() removes blanks from the beginning of a string instead of end.

RTRIM() and LTRIM() functions can be used with any a constant, variable, or column of either character or binary data. But you need to make sure that
expression whixh you want to use with these functions must be of a data type that is implicitly convertible to varchar. Otherwise, you need to use CAST to explicitly convert expression.

Continue reading “RTRIM() and LTRIM() functions in SQL Server”