String Functions in SQL Server Part 1

In this article I would like to give you an overview on various string functions that are available in SQL Server and are widley used at work.

In this IT world 50% of the databases consist of CHAR, NCHAR, VARCHAR, and NVARCHAR as data types to store the values. When you are joining the tables based on the string family columns then you need to use string functions in order to return right output from your queries or modify t he out put to make it more meaningful.

Why do we need to use string functions?
Instead of writing couple of lines explaining about this, I would like explain with a simple example.
Let’s look at the below table:

CREATE TABLE EMPLOYEE
(EmployeeID INT,
FirstName VARCHAR(10),
MiddleName VARCHAR(10),
LastName VARCHAR(10)
);

Use the below statements to insert the data:

Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (1, 'steve', 'Kris', 'Burg');
Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (2, 'StEve', 'NICK', 'ross');
Insert into EMPLOYEE (EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values (3, 'STEVE', 'A', 'FOX');

If you look at the data, Names are stored in mixed case.
EmployeeID = 3 stored in upper case, EmployeeID = 2 stored in mixed case and EmployeeID = 1 in Title Case.
So if you want to find out all the employees whose first name is “steve” with out looking at the case? This is where string functions come into the picture.

1) UPPER : UPPER(Input)
The UPPER() function converts all the input characters in a string into uppercase.
Let's look at the below example where we are converting the FirstName to upper case.

SELECT UPPER(FirstName) as FirstName FROM EMPLOYEE

FirstName
-------------
STEVE
STEVE
STEVE

2) SUBSTRING: SUBSTRING(expression,start_integer,length_integer)
The SUBSTRING() function is bascailly returns a part of the string based on the starting point to specified number of characters.

SELECT SUBSTRING(FirstName,1,3) as First_3 FROM Employee

First_3
-----------
ste
StE
STE

The function returned three characters from the FirstName

3) STUFF : STUFF(char_expression1,start_integer,length_integer,char_expression2)

The STUFF() function is very similar to REPLACE function where it is used replaces certain characters not based on the pattern but based on the starting position and length. It replaces characters in char_expression1 based on length_integer starting from start_integer with the char_expression2.

Let's look at the example:

SELECT
STUFF('STEVESTEVE',6,5,NULL) AS remove_multiple1,
STUFF('STEVESTEVE',6,5,'') AS remove_multiple2
remove_multiple1 remove_multiple2
-------------------- ----------------
STEVE STEVE

4) SPACE: SPACE(expression)
This function is used to return No.Of spaces or blanks based on the expression.
SELECT LEN(SPACE(24) + 'v') AS No_of_Spaces No_of_Spaces
---------------
25

We will continue next set of string functions in my next article.