String Functions Used In Sql Server

1. ASCII(Expression)

Returns the ASCII code value of a expression

Arguments	Data type	Description	Return
Expression	Char or Varchar	Fixed-length non-Unicode character data with length of n bytes	Integer

Example: 

2. CHAR(Integer_Expression) 

Char function converts the integer ASCII code value to a character. 

Arguments	Data type	Description	Return
Integer_Expression	Tinyint	The integer_expression should be from 0 to 255. If the value of integer is above 255, NULL is returned	Char(1)

Example: 

3. CHARINDEX(‘Search_Expression’, ‘Expression_To_Be_Search’, ‘[Start_Location]’)

Searches an expression and returns the starting position of the specified expression in character string 

Arguments	Data Type	Description	Return
Search_Expression	Varchar	Is the string expression to be searched	Integer or Bigint
Expression_to_be_search	Varchar	The string or number or an alphanumeric value to search within.
Start_location	Bigint	Optional, At which position search will start. The first position is 1

Example:

4. SOUNDEX(Expression)

Soundex converts an alphanumeric string to a four-character code based on how the string sounds when spoken. 

The first character of the string are converted into Upper case and through second character are converted into three numbers based on Soundex guide. Zeros are added at the end if necessary to produce a four-character code. Additional letters are disregarded

Soundex Coding Guide

Number Represents the letters

Number	Represents the letters
1.	B,F,P,V
2.	C,G,J,K,Q,S,X,Z
3.	D,T
4.	L
5.	M,N
6.	R

Disregard the letters A,E,I,O,U,H,W and Y

Arguments	Data type	Description	Return
Expression	Varchar	Fixed-length non-Unicode character data with length of n bytes	Varchar

Example: 

5. DIFFERENCE(Expression 1, Expression 2)

To compare 2 character strings and returns the difference between the soundex values of specified strings. Difference can return from 0 to 4. 0 means little or no similarity and 4 means strong similarity or identical values. 

Arguments	Data Type	Description	Return
Expression1	Varchar	Fixed-length non-Unicode character data with length of n bytes	Integer
Expression2	Varchar	Fixed-length non-Unicode character data with length of n bytes

Example: 

6. LEFT(Expression,Number_Of_Characters)

Returns the specified number of characters from the left hand side of the given character expression.

Arguments	Data Type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar
Number_Of_Characters	Integer	Specifies how many characters of the string to extract and return

Example:

7. RIGHT(Expression,Number_Of_Characters)

Returns the specified number of characters from the right hand side of the given character expression.

Arguments	Data Type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar
Number_Of_Characters	Integer	Specifies how many characters of the string to extract and return

Example:

8. LEN(Expression)

Returns the length of the specified string. The length is returned excluding any trailing blank characters.

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Integer  or BigInt

Example:

9. LOWER(Expression)

Converts a character of the string to lower case.

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar

Example:

10. UPPER(Expression)

Converts a character of the string to upper case.

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar

Example:

11. LTRIM(Expression)

Removes blanks at the left hand side of the given character expression

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar

Example:

12. RTRIM(Expression)

Removes blanks at the right hand side of the given character expression

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar

Example:

13. NCHAR(Integer_Expression)

Returns the Unicode character of the specified integer expression.

Arguments	Data type	Description	Return
Expression	Integer	A positive whole number from 0 through 65535	nChar(1)

Example:




14. PATINDEX(Search_pattern_literal,Expression_to_search)

Returns the starting position of the first occurrence of a pattern in a specified expression. It takes two arguments, the pattern to be searched and the expression. PATINDEX() is similar to CHARINDEX() where in CHARINDEX() we cannot use wildcards, whereas PATINDEX() provides this capability. If the specified pattern is not found, PATINDEX() returns ZERO

Arguments	Data Type	Description	Return
Search_pattern_literal	Varchar or nVarchar	Is the string expression to be searched	Integer or  Bigint
Expression_to_be_search	Varchar or nVarchar	The string or number or an alphanumeric value to search within.

Example:

15. QUOTENAME(Expression,[Optional] Quote_Character)

It accepts a string as input and returns string of a valid delimited identifier according to defined pattern.

Arguments	Data Type	Description	Return
Expression	nVarchar(128)	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar(258)
Quote_character	Char(1)	Optional, one character string to use as the delimiter

Example:



16. REPLACE(Expression_To_Be_Search,Search_Expression,Replacement_Expression)

Replaces all occurrence of a specified string value with another string value.

Arguments	Data Type	Description	Return
Expression_to_be_search	Varchar or nVarchar	The string or number or an alphanumeric value to search within.	Varchar or nVarchar
Search_Expression	Varchar or nVarchar	Is the string expression to be searched
Replacement_Expression	Varchar or nVarchar	Is the replacement string

Example:

17. REPLICATE(String_To_Be_Replicate,No_Of_Times_To_Be_Replicate)

Repeats the given string for the specified no. of times

Arguments	Data Type	Description	Return
String_to_be_Replicate	Varchar or nVarchar	Is a string to be repeat	Varchar or nVarchar
No_of_times_to_be_replicate	Bigint	Is the number of times the string to be repeated

Example:



18. REVERSE(EXPRESSION)

String function returns the string in reverse order.

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Varchar or nVarchar

Example:

19. SPACE(EXPRESSION)

Returns no. of spaces, specified by the no. of spaces argument

Arguments	Data type	Description	Return
Expression	Integer	The number of spaces to be returned	Char

Example:




20. STR(Float_Expression,[Optional] Length,[Optional] Scale)

Returns character data converted from numeric data

Arguments	Data Type	Description	Return
Float_Expression	Float	Is an expression of approximate Float data type	Varchar
Length	Int	Optional, Default value of length is 10. If you specify negative value then it returns NULL. It should be greater than or equal to the part of the number before the decimal point plus the number’s sign. If it is not so then it returns **
Scale	Int	Optional, The number of decimal places in the resulting string and cannot exceed 16. If decimal_places is not specified, it will default to 0

Example:




21. STUFF(Original_expression,Startposition,Number_of_characters,Replacement_expression)

Inserts replacement expression at the start position specified along with removing the characters specified using length parameters.

Arguments	Data Type	Description	Return
Original_expression	Varchar, nVarchar or Varbinary	Fixed-length non-Unicode character data with length of n bytes	Varchar, nVarchar or Varbinary
Start_position	Bigint	Is an integer value that specifies the location to start insertion or deletion
Number_of_characters	Bigint	Is an integer that specifies the number of characters to delete
Replacement_expression	Varchar, nVarchar or Varbinary	This expression will replace length characters of replacement_expression beginning at start.

Example:

22. SUBSTRING(Expression,Start_Position,Length)

Returns substring (part of the string) from the given expression

Arguments	Data Type	Description	Return
Expression	Varchar, nVarchar, Text, nText, Varbinary, Image	Is  charaacter ,binary, text, ntext, or image expression	Varchar, nVarchar, Text, nText, Varbinary, Image
Starting_position	Bigint	specifies where the returned characters start
Length	Bigint	specifies how many characters of the expression will be returned

Example:

23. UNICODE(Expression)

Returns the integer value, as defined by the Unicode standard for the first character of the input

Arguments	Data type	Description	Return
Expression	Varchar or nVarchar	Fixed-length non-Unicode character data with length of n bytes	Integer

Example: