SPACE() and REPLACE() functions in SQL Server

 

SPACE()

The SPACE() function returns the specified number of the spaces. This function is mainly useful for formatting.

Summary:

SPACE ( Integer_Value)

Integer_Value is the numbers of repeated spaces. (If integer is negative,a null string is returned.)

Example:

Suppose we have a table ‘tbl_employee’

Table name: tbl_Employee

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

see this query:

select EmpName + space(5) + Department as [Format string] from tbl_employee where Department=’HR’

Output Will be:

Format String
Ankur       HR
David       HR

 

REPLACE()

And REPLACE() function have three parameters. All occurrence of the second string in the first string are replaced with the value of third string.

Summary:

REPLACE(stringExp1, StringExp2, StringExp3)

Example:

Suppose we have a table ‘tbl_employee’

Table name: tbl_Employee

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

The following  query show the result after replacing ‘HR’ to “Human Resource’.

select EmpName,REPLACE(Department,’HR’,’Human Resource’) from tbl_Employee where Department=’HR’

Output Will be:

AnkurHuman Resource
DavidHuman Resource