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