Overview:
I would like to give you an overview on various string functions that are available in SQL Server and are widles used in work, in series of articles with real examples.
Introduction:
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 on your queries.
Why do we need to use string functions?
Instead of writing couple of lines on why do you need this, I would like explain with an example.
Let’s look at the below table:
CREATE TABLE EMPLOYEE (
EmployeeID INT,
FirstName VARCHAR2(10),
MiddleName VARCHAR2(10),
LastName VARCHAR2(10));
Use the below statements to insert the data
Insert into EMPLOYEE
(EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values
(1, 'steve', 'Kris', 'Burg');
Insert into EMPLOYEE
(ROWID, EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values
(2, 'StEve', 'NICK', 'ross');
Insert into EMPLOYEE
(ROWID, EMPLOYEEID, FIRSTNAME, MIDDLENAME, LASTNAME)
Values
(3, 'STEVE', 'A', 'FOX');
COMMIT;
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.
Now you know why we need to use string functions. Now let’s get into the details.
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 uppercase.
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.
|
No responses found. Be the first to respond and make money from revenue sharing program.
|