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 Id EmpName Salary Department
1 Ankur 1200 Sales
2 David 3400 Sales
3 John 1500 HR
4 James 1200 Production
5 Mohan 4500 Production
6 Ram 2300 HR

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:

NameOfLength EmpName
4 John
3 Ram

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:

EmpName EmpCode
Ankur ANK-00001
David DAV-00002
John JOH-00003
James JAM-00004
Mohan MOH-00005
Ram RAM-00006