StringDistance Function

The StringDistance() function simply finds the number of characters which needs to be changed for two strings to match. It takes two (2) or three (3) argument as text and returns an integer value. It works similar to Levenshtein Distance Algorithm. It can perform case sensitive or case insensitive comparison. By default it uses StringComparison.OrdinalIgnoreCase sort rules.

The syntax for StringDistance function is StringDistance( String , String , [ String Comparison Type ]).

SELECT StringDistance ('Another', 'Bother') /* returns 2 since 'An' needs to be changed. */ ;


SELECT StringDistance ('Boo', 'Woo') /* returns 1 due to B and W */ ; 


SELECT StringDistance ('confirmation', 'confirmation') /* returns 0 */ ; 


SELECT StringDistance ('Database', 'database') /* returns 0 */ ; 


SELECT StringDistance ('Database', 'database', 'Ordinal') /* returns 1 */ ;

To find distance between two strings in Case Sensitive manner use .Net StringComparison Enum to do the comparison of text, as shown in above example with three parameters passed. Following are valid values when supplied as third argument.

Argument Value	Comparison Type
CurrentCulture	Compare strings using culture-sensitive sort rules and the current culture.
CurrentCultureIgnoreCase	Compare strings using culture-sensitive sort rules, the current culture, and ignoring the case of the strings being compared.
InvariantCulture	Compare strings using culture-sensitive sort rules and the invariant culture.
InvariantCultureIgnoreCase	Compare strings using culture-sensitive sort rules, the invariant culture, and ignoring the case of the strings being compared.
Ordinal	Compare strings using ordinal (binary) sort rules.
OrdinalIgnoreCase	Compare strings using ordinal (binary) sort rules and ignoring the case of the strings being compared.
Binary	Compare strings using ordinal (binary) sort rules.
NoCase	Compare strings using ordinal (binary) sort rules and ignoring the case of the strings being compared.

 /* returns 1 as D and d are not same */ 
SELECT StringDistance ('Database', 'database' , 'Binary');


/* returns 2 takes space between words and D in account */
SELECT StringDistance ('SQLDatabase', 'SQL database' , 'Binary')  ;

		
 /* returns 1 takes only space as different */
SELECT StringDistance ('SQLDatabase', 'SQL database' , 'OrdinalIgnoreCase') ;

String Distance is very useful to find spelling variations and possible duplicates.