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 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 |
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 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 |
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:
Ankur | Human Resource |
David | Human Resource |