String Functions in Sql Server- Part1
In this article i explain about the Built-in String Functions in Sql Server. String Functions present in this part are Upper, Lower, Ltrim, Rtrim, Left, Right, Ascii, Unicode, Char, Nchar, Reverse, Replace, Len, Stuff. These string functions are used to format the string and in addition they are used to concatenate or compare strings.
UPPER:
Upper function change the output into upper case.
LOWER:
Lower function change the output into lower case.
LTRIM:
Ltrim removes the leading blanks. The data are left aligned.
RTRIM:
Rtrim removes the trailing characters. It truncate all trailing blanks.
LEFT:
Left function retrieve the first few characters from the left of the given string.
RIGHT:
Right function retrieve the portion of the string counting from the right.
select UPPER('dotnet') as Upper, LOWER('DOTNET') as Lower, RTRIM('Ram ')+'Gopal' as Rtrim,
LTRIM(' Trichy') as Ltrim, LEFT('RamGopal',3) as Left, RIGHT('RamGopal',5) as Right
Output:
Upper Lower Rtrim Ltrim Left Right
DOTNET dotnet RamGopal Trichy Ram Gopal
ASCII:
It returns the ASCII code value of the leftmost character of a string.
UNICODE:
It works just like ASCII, except it accepts the Unicode character value as input. This could be useful if you're working with international character sets.
CHAR:
Char function does the opposite of ASCII - it returns an alphanumeric equivalent of an ASCII code.
NCHAR:
Nchar function works exactly like CHAR except it returns the Unicode character. This function is useful if you're working with large international character sets.
select ASCII('A') as ASCII, CHAR(65) as CHARACTER, UNICODE('1') as UNICODE, NCHAR(49) as NCHAR
Output:
ASCII CHARACTER UNICODE NCHAR
65 A 49 1
REVERSE:
Reverse function gives you a mirror image of a given string.
REPLACE:
This function replaces some characters within a string with another set of characters.
LEN:
Len function finds the length of the character string.
STUFF:
Stuff function inserts a set of characters into a given string at a given position.
select REVERSE('DOTNET') as Reverse, REPLACE('122345',2,0) as Replace, LEN('DOTNET') as Length,
STUFF('AIJDEF',2,2,'BC') as Stuff
Output:
Reverse Replace Length Stuff
TENTOD 100345 6 ABCDEF