Overview:
In my previous article I covered few string functions that we use in our day-2-day life. Now in this article I will focus on few more.
Well if you have not got a chance to read my previous article then check out the below link.
http://www.dotnetspider.com/resources/19776-String-Functions-SQL-Server-Part.aspx
1) REVERSE : REVERSE(expression)
.redro esrever ni tupni eht nruter ot desu si noitcnuf sihT. oops, in what language am i writing? Ohh you know what, this what the function does. This will simply reverses the input string.
Let’s look at the example:
SELECT
REVERSE('STEVE') AS Reverse1,
REVERSE(12345) AS Reverse2
Reverse1 Reverse2
-------- --------
EVETS 54321
2) REPLICATE: REPLICATE (expression, times_integer)
This REPLICATE function is used to repeat the same string several times based on the times_integer parameter specified. The return data type will be implicitly converted into VARCHAR.
Let's look at the below example:
SELECT
REPLICATE('V',5) AS replicate1,
REPLICATE(6,2) AS replicate2
replicate1 replicate2
---------- ----------
VVVVV 66
3) REPLACE: REPLACE (string_expression1, string_expression2, string_expression3)
This function is used to replace all occurrences of the second expression within the first expression with the third expression.
Let’s look at the below example:
SELECT
REPLACE('VIJAYA','AYA','*') AS replace1
replace1
--------
VIJ
4) PATINDEX: PATINDEX(pattern_expression,string_expression)
The PATINDEX (i.e PATtern INDEX) function returns the starting position of the first occurence of "pattern expression" in the "string expression". If the pattern is not found, then zeroes are returned.
Lets look at the below example:
SELECT
PATINDEX('%STE%','STEVE AUSTIN') AS Patindex1,
PATINDEX('%NY%','NEW YORK') AS Patindex2
Patindex1 Patindex2
--------- ---------
1 0
5) LTRIM: LTRIM(char_expression)
This LTRIM (i.e. LeftTRIM) function is used to trim the spaces or blanks on the left side of the string.
SELECT
( '*' + LTRIM (' STEVE ') + '*') AS ltrim1,
ltrim1
------
*STEVE *
6) LTRIM: RTRIM(char_expression)
This RTRIM (i.e. RightTRIM) function is used to trim the spaces or blanks on the Right side of the string.
SELECT
( '*' + LTRIM (' STEVE ') + '*') AS ltrim1,
ltrim1
------
* STEVE*
Now if you want to trim on both the sides then you need to use LTRIM and RTRIM.
7) LEN: LEN(expression)
The LEN (i.e. LENgth) function returns the length of the expression.
One important point that we need to consider here is the leading blanks are included in the calculation, while trailing blanks are not.
Let’s look at the below example:
SELECT LEN(' VIJAYA ') AS len1
len1
----
7
The above query returns 7, taking into consideration the one leading blank, but ignoring the four trailing blanks:
With this I will end this article, hoping that I have covered the string functions that we use in our day-to-day life.
Summary
Happy learning
|
No responses found. Be the first to respond and make money from revenue sharing program.
|