String Functions in Sql Server- Part2
In this article i explain about the Built-in String Functions in Sql Server. String Functions present in this part are Soundex, Difference,
Quotename, Space, Charindex, Patindex, Substring, Replicate, Str. These string functions are used to format the string and
in addition they are used to concatenate or compare strings.
SOUNDEX:
Soundex function provides a four character representation of the string called SOUNDEX code. It is used to determine whether two strings sound alike.
DIFFERENCE:
Difference function provides a degree of similarity between the two character expressions. The highest degree of similarity is 4.
select SOUNDEX('sine') as Code1, SOUNDEX('sign') as Code2 ,
DIFFERENCE('sine','sign') as Difference
OUTPUT:
Code1 Code2 Difference
S500 S250 3
QUOTENAME:
Quotename appends square brackets to the beginning and end of the string expression.
SPACE:
This function creates a number of spaces you want to print.
select QUOTENAME('I have brackets') as Quotename, 'DotNet'+SPACE('2')+'Spider' as Space
OUTPUT:
Quotename Space
[I have brackets] DotNet Spider
CHARINDEX:
Charindex finds the position of the first occurrence of a character or multiple characters.
PATINDEX :
Patindex also finds the position of the first occurrence of a character or multiple characters. The difference is that you have to append % wildcards to PATINDEX. This function searches for a pattern.
select CHARINDEX('Net','DotNet Spider') as Charindex,
PATINDEX('%Net%','DotNet Spider') as Patindex
OUTPUT:
Charindex Patindex
4 4
SUBSTRING:
Substring retrieves a portion of the string starting at the specified character which contains the number of characters specified.
REPLICATE:
Replicate function repeats a given string for the specified number of times.
STR:
Str function converts a numeric value into a string value.
select SUBSTRING('DOTNET',1,3) as Substring, REPLICATE('1',5) as Replicate,
STR('2500.44',7) as Str
OUTPUT:
Substring Replicate Str
DOT 11111 2500