String Functions in SQL Server -- Final Part

In this article I would like cover and complete rest of the String Functions.

1) CHARINDEX: CHARINDEX (expression1 ,expression2 [ , start_location ] )
expression1 Is sequence of characters that to be found in expression2. start_location is optional parameter. This will be useful if you want to search the expression1 in expression2 from a specific location.
In simple words this is used to find the starting position of a string (i.e. expression1) with in another string (expression2).


SELECT CHARINDEX('Kadiyala', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
8

SELECT CHARINDEX('ya', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
5

The below example is based on Start Location:
SELECT CHARINDEX('ya', 'Vijaya Kadiyala',7) AS String_position

Output Is

String_position
---------------
12

2) DIFFERENCE: DIFFERENCE(Expression1, expression2)
This function is used to find the similarity between two expressions. This is very similar to SOUNDEX in fact this is a wraper over SOUNDEX. This function returns value from 0 to 4. 0 means no similarity and 4 means very close similarity. In simple words this is used to search for Phonetically Similar Data.

SELECT DIFFERENCE('Vijaya','KADIYALA') AS SIMILARITY

Output Is

SIMILARITY
-----------
2

SELECT DIFFERENCE('Vijaya','VJA') AS SIMILARITY

Output Is

SIMILARITY
-----------
4

3) PATINDEX: PATINDEX (expression1 ,expression2)
expression1 Is sequence of characters that to be found in expression2.
In simple words this is used to find the starting position of a string (i.e. expression1) with in another string (expression2). So what is the difference between PATINDEX and CHARINDEX? Well PATINDEX can be used with Wild characters but not with CHARINDEX. This is similar to LIKE operation. The big advantage with this is you can even use Regular Expressions.

SELECT PATINDEX('%y_l%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
12

(1 row(s) affected)

SELECT PATINDEX('%a_a%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
4

(1 row(s) affected)


SELECT PATINDEX('%a[^y]a%', 'Vijaya Kadiyala') AS String_position

Output Is

String_position
---------------
13

(1 row(s) affected)

4) REPLICATE: REPLICATE (expression ,No_Of_Times)
This function is used to copy the same string again and again based on No_Of_Times parameter.

SELECT REPLICATE('www.DotNetVJ.com',3) as REP

Output Is
REP
------------------------------------------------
www.DotNetVJ.comwww.DotNetVJ.comwww.DotNetVJ.com

(1 row(s) affected)

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

Output Is

ltrim1
-------------
*STEVE *

(1 row(s) affected)


6) RTRIM: 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

Output Is

ltrim1
-------------
*STEVE *

(1 row(s) affected)

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

Output Is


len1
-----------
7

(1 row(s) affected)


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.