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 function provides a four character representation of the string called SOUNDEX code. It is used to determine whether two strings sound alike.
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

Code1 Code2 Difference
S500 S250 3

Quotename appends square brackets to the beginning and end of the string expression.
This function creates a number of spaces you want to print.

select QUOTENAME('I have brackets') as Quotename, 'DotNet'+SPACE('2')+'Spider' as Space

Quotename Space
[I have brackets] DotNet Spider

Charindex finds the position of the first occurrence of a character or multiple characters.
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

Charindex Patindex
4 4

Substring retrieves a portion of the string starting at the specified character which contains the number of characters specified.
Replicate function repeats a given string for the specified number of times.
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

Substring Replicate Str
DOT 11111 2500


  • Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.
  • No HTML formatting and links to other web sites are allowed.
  • This is a strictly moderated site. Absolutely no spam allowed.
  • Name: