.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Articles >> DataBase >> Post New Resource Bookmark and Share   

 Subscribe to Articles

String functions in Sql server

Posted By:Deco       Posted Date: October 25, 2010    Points: 50    Category: DataBase    URL: http://www.dotnetspark.com  

String function are used to manipulate the string. In this article let us see the string function and how it's really helpful in manipulating the strings.
 

Sql server has many string functions, Let's see one by one

Substring:
-----------

Syntax of the substring function is as follows

SubString(string, starting position, length)
Returns a portion of a string. The first parameter is the string, the second parameter is the beginning position of the substring to be extracted, and the third parameter is the length of the string extracted:

SELECT SUBSTRING('abcdefg', 3, 2)
Result:
cd
Stuff() function:
-----------------
Syntax of the stuff function is as follows;

Stuff(string, insertion position, delete count, string inserted)

The inverse of substring(), the stuff() function inserts one string into another string.
The inserted string may delete a specified number of characters as it is being inserted:

SELECT STUFF('abcdefg', 3, 2, '123')
Result:
ab123efg

CharIndex:
------------

Syntax of the CharIndex function;

CharIndex(search string, string, starting position)
CharIndex function returns the character position of a string within a string.

SELECT CHARINDEX('c', 'abcdefg', 1)
Result:
3

PatIndex:
----------

Syntax of the PatIndex is as follows;

PatIndex(%pattern%, string)
PatIndex  Searches for a pattern, which may include wildcards, within a string.
The following code locates the first position of either a c or d in the string.

SELECT PATINDEX('%[cd]%', 'abdcdefg')
Result:
3
Right and Left function:
-------------------------

Right(string, count)
Left(string, count)

It returns the rightmost or leftmost part of a string.

SELECT Left('Nielsen',2) AS '[Left]',
RIGHT('Nielsen',2) AS [Right]

Result:
Left Right
----- ----
Ni    en


Len function:
--------------
Len(string)
 
It returns the length of a given string.

SELECT LEN('Supercalifragilisticexpialidocious') AS Len
Result:
Len
----
34

RTrim and LTrim:
------------------
Rtrim(string)
Ltrim(string)
Removes leading or trailing spaces. While it's difficult to see in print, the three leading and trailing spaces are removed from the following string. I adjusted the column-header lines with the remaining spaces to illustrate
the functions:
SELECT RTRIM(' middle earth ') AS [RTrim],
LTRIM(' middle earth ') AS [LTrim]

Result:

RTrim                LTrim
---------------------------
middle earth   middle earth
Upper and Lower function:
-----------------------------

Syntax for both upper and lower are as follows;

Upper(string)
Lower(string)

It converts the entire string to uppercase or lower case. Minuscules, or lowercase letters, were first used in the ninth century to facilitate handwriting. With the advent of the printing press in the fifteenth century, printers manually set the type for each page printed. They stored the letters in cases above the page box. The initials (capital letters) were stored above the minuscules. The terms "uppercase" and "lowercase" stuck.

Select UPPER('one TWO tHrEe') as [UpperCase],
LOWER('one TWO tHrEe') as [LowerCase]
Result:
UpperCase      LowerCase
---------------------------
ONE TWO THREE one two three

Replace:
----------

Syntax is as follows

Replace(Column,string,string)
The replace() function operates as a global search and replace within a string. Using replace() within an update DML command can quickly fix problems in the data, such as removing extra tabs or correcting string patterns.

SELECT Replace('Alwyn Duraisingh', 'y', 'i')

Result:
Alwin Duraisingh

 Subscribe to Articles

     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More Articles on C#, ASP.Net, Vb.Net, SQL Server and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend