String Functions in SQL Server

Introduction

 
SQL Server has a variety of string functions that are very useful for manipulating the string data type. SQL Server uses various data types to store the string and character types of data (in other words varchar, nvarchar, and char). So we can use the string functions to get the desired and specific results.
 
SQL Server has the following string functions: 
 
SQL Server
 
All these functions are very simple. I think all developers are aware of all of the system functions. But I think beginners and beginners are not so aware of all of these functions. So I am writing a series of articles on System functions.
 
This is the second part of this series. The first article of this series is Mathematical functions. If you want to read this article then use this link.
Now we read each string function one by one.
 
ASCII
 
 
The ASCII function returns the ASCII code value of the leftmost character of a character expression.
 
 
Syntax
 
 
ASCII ( character_expression )
 
 
Return Type: int
 
 
varchar
 
 
Example
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHARCHAR,  
  6. [ASCII] INT,  
  7. )  
  8. SET @STR='PANkaj';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT SUBSTRING(@STR,@INT,1) AS [CHAR] , ASCII(SUBSTRING(@STR,@INT,1)) [ASCII]  
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
type
 
 
CHAR
 
 
The CHAR function converts an int ASCII code to a character.
 
 
Syntax
 
 
CHAR ( integer_expression )
 
 
Return Type
 
 
char(1)
 
 
CHAR
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHARCHAR,  
  6. [ASCII] INT,  
  7. )  
  8. SET @STR='PANkaj';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT CHAR(ASCII(SUBSTRING(@STR,@INT,1))) [CHAR] , ASCII(SUBSTRING(@STR,@INT,1)) AS [ASCII]   
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
ASCII
 
 
CHARINDEX
 
 
The CHARINDEX function searches an expression for another expression and returns its starting position if found.
 
 
Syntax
 
 
CHARINDEX ( expressionToFind ,expressionToSearch [ , start_location ] )
 
 
If expressionToFind is not found within expressionToSearch, CHARINDEX returns 0.
 
 
Return type
 
 
bigint if expressionToSearch is of the varchar(max), nvarchar(max), or varbinary(max) data types; otherwise, int.
 
 
otherwise
 
 
Example
 
  1. DECLARE @expressionToSearch [varchar](MAX);  
  2. DECLARE @expressionToFind [varchar](MAX);  
  3. SET @expressionToSearch ='SQL SERVER CONTAIN STRING FUNCTION'  
  4. SET @expressionToFind='SERVER';  
  5.   
  6. SELECT CHARINDEX(@expressionToFind,@expressionToSearch ) AS LOCATION_IS;  
Output
 
 
expressionToSearc
 
 
Example
 
  1. DECLARE @expressionToSearch [varchar](MAX);  
  2. DECLARE @expressionToFind [varchar](MAX);  
  3. SET @expressionToSearch ='SQL SERVER CONTAIN STRING FUNCTION SERVER'  
  4. SET @expressionToFind='SERVER';  
  5.   
  6. SELECT CHARINDEX(@expressionToFind,@expressionToSearch ,7) AS LOCATION_IS;  
Output
 
 
abc
 
 
LEFT
 
 
The LEFT function returns the left part of a character string with the specified number of characters.
 
 
Syntax
 
 
LEFT ( character_expression , integer_expression )
 
 
Return Type 
  • Varchar: When character_expression is a non-Unicode character data type.
  • Nvarchar: When character_expression is a Unicode character data type.
LEFT
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHAR] [varchar](MAX),  
  6. [LENGTH] INT,  
  7. )  
  8. SET @STR='PANKAJ';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT LEFT(@STR,@INT) [STRING] , @INT [LENGTH]   
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
see
 
 
LEN
 
 
The LEN function returns the number of characters of the specified string expression, excluding trailing blanks.
 
 
Syntax
 
 
LEN ( string_expression )
 
 
Return Type
 
 
bigint if expression is of the varchar(max), nvarchar(max) or varbinary(max) data types; otherwise, int.
 
 
bigint
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHAR] [varchar](MAX),  
  6. [LENGTH] INT,  
  7. )  
  8. SET @STR='PANKAJ';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT LEFT(@STR,@INT) [STRING] , LEN(LEFT(@STR,@INT))  
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
LEN
 
 
LOWER
 
 
The LOWER function returns a character expression after converting uppercase character data to lowercase.
 
 
Syntax:
 
 
LOWER ( character_expression )
 
 
Return Type
 
 
varchar or nvarchar
 
 
LOWER Return
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHAR] [varchar](MAX),  
  6.   
  7. )  
  8. SET @STR='PANKAJ';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT LOWER(LEFT(@STR,@INT)) [STRING]   
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
LOWER
 
 
LTRIM
 
 
The LTRIM function returns a character expression after it removes leading blanks.
 
 
Syntax
 
 
LTRIM ( character_expression )
 
 
Return Type
 
 
varchar or nvarchar
 
 
LTRIM Return
 
 
Example
 
  1. DECLARE @LTRIM varchar(60);  
  2. DECLARE @RTRIM varchar(60);  
  3. SET @LTRIM = ' I HAVE 6 SPACE ON LEFT SIDE';  
  4. SET @RTRIM='I HAVE 6 SPACE ON RIGHT SIDE ';  
  5. SELECT 'STRING WITHOUT LEFTSPACE ' +  
  6. LTRIM(@LTRIM) UNION ALL  
  7. SELECT 'SPACE OF RIGHTSIDE NOT REMOVE ' +  
  8. LTRIM(@RTRIM)+'REMOVE';  
  9. GO  
Output
 
 
LTRIM image
 
 
NCHAR
 
 
The NCHAR function returns the Unicode character with the specified integer code, as defined by the Unicode standard.
 
 
Syntax
 
 
NCHAR ( integer_expression )
 
 
Return Type
 
 
nchar(1) when the default database collation does not support supplementary characters.
 
 
NCHAR
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5.   
  6. VALUE INT,  
  7. [CHAR] [nvarchar](MAX)  
  8. )  
  9. SET @STR='P#n()J@';  
  10. SET @INT=1;  
  11. WHILE @INT<=DATALENGTH(@STR)  
  12. BEGIN  
  13. INSERT INTO #TAB   
  14. SELECT UNICODE(SUBSTRING(@STR,@INT,1)) [STRING] , NCHAR(UNICODE(SUBSTRING(@STR,@INT,1))) [VALUE]  
  15. SET @INT=@INT+1;  
  16. END  
  17. SELECT * FROM #TAB t  
  18. DROP TABLE #TAB;  
Output
 
 
DROP TABLE photo
 
 
PATINDEX
 
 
The PATINDEX function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.
 
 
Syntax
 
 
PATINDEX ( '%pattern%' , expression )
 
 
Return Type
 
 
bigint if expression is of the varchar(max) or nvarchar(max) data types; otherwise int.
 
 
PATINDEX
 
 
Example
 
  1. DECLARE @PATSTRING [nvarchar](MAX);  
  2. DECLARE @PATTERN [nvarchar](MAX);  
  3. SET @PATSTRING='I HAVE A PATTERN STRING';  
  4.   
  5. SELECT PATINDEX('%PA%', @PATSTRING) [INDEX], 'PA' PATTERN UNION ALL  
  6.   
  7. SELECT PATINDEX('%P_T%', @PATSTRING) [INDEX], 'P_T' PATTERN UNION ALL  
  8.   
  9. SELECT PATINDEX('%S_R__G%', @PATSTRING) [INDEX], 'S_R__G' PATTERN  
Output
 
 
SELECT PATINDEX
 
 
REPLACE
 
 
REPLACE function Replaces all occurrences of a specified string value with another string value.
 
 
Syntax
 
 
REPLACE ( string_expression , string_pattern , string_replacement )
 
 
Return Type
 
 
Returns nvarchar if one of the input arguments is of the nvarchar data type; otherwise, REPLACE returns varchar.
 
 
Returns NULL if any one of the arguments is NULL.
 
 
If string_expression is not of type varchar(max) or nvarchar(max), REPLACE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to a large-value data type.
 
 
REPLACE
 
 
Example
 
  1. DECLARE @PATTERN [nvarchar](MAX);  
  2. DECLARE @FIND [nvarchar](MAX);  
  3. DECLARE @REPLACEWITH [nvarchar](MAX);  
  4.   
  5. SET @PATTERN='I LIKE ENGLISH';  
  6. SET @FIND='ENGLISH';  
  7. SET @REPLACEWITH='HINDI';  
  8.   
  9. SELECT REPLACE(@PATTERN,@FIND,@REPLACEWITH) [REPLACE];  
Output
 
 
SELECT REPLACE
 
 
QUOTENAME
 
 
QUOTENAME function Returns a Unicode string with the delimiters added to make the input string a valid SQL Server delimited identifier.
 
 
Syntax
 
 
QUOTENAME ( 'character_string' [ , 'quote_character' ] )
 
 
Return type
 
 
nvarchar(258)
 
 
nvarchar
 
 
Example
 
  1. DECLARE @QUTO1 [nvarchar](MAX);  
  2. DECLARE @QUTO2 [nvarchar](MAX);  
  3. DECLARE @QUTO3 [nvarchar](MAX);  
  4.   
  5. SET @QUTO1='HINDI';  
  6. SET @QUTO2='HI[NDI';  
  7. SET @QUTO3='HI[]]]''NDI';  
  8.   
  9. SELECT QUOTENAME(@QUTO1) [QUTONMAE] UNION ALL  
  10. SELECT QUOTENAME(@QUTO2) [QUTONMAE] UNION ALL  
  11. SELECT QUOTENAME(@QUTO3) [QUTONMAE]  
Output
 
 
QUOTENAME
 
 
REVERSE
 
 
REVERSE function Returns the reverse order of a string value.
 
 
Syntax
 
 
REVERSE ( string_expression )
 
 
Return Type
 
 
varchar or nvarchar
 
 
REVERSE
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [STRING] [varchar](MAX),  
  6. [REVERSE] [varchar](MAX)  
  7. )  
  8. SET @STR='PANKAJ';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT LEFT(@STR,@INT) [STRING] , REVERSE(LEFT(@STR,@INT) ) [REVERSE]  
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
INSERT INTO
 
 
REPLICATE
 
 
REPLICATE function Repeats a string value a specified number of times.
 
 
Syntax
 
 
REPLICATE ( string_expression ,integer_expression )
 
 
Return Type
 
 
Returns the same type as string_expression.
 
 
REPLICATE
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [STRING] [varchar](MAX),  
  6.   
  7. )  
  8. SET @STR='DEMO';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT REPLICATE('0',@INT)+@STR;  
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
TAB image
 
 
RIGHT
 
 
The RIGHT function returns the right part of a character string with the specified number of characters.
 
 
Syntax
 
 
RIGHT ( character_expression , integer_expression )
 
 
Return Type
 
 
Returns varchar when character_expression is a non-Unicode character data type.
 
Returns nvarchar when character_expression is a Unicode character data type.
 
 
RIGHT
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHAR] [varchar](MAX),  
  6. [LENGTH] INT,  
  7. )  
  8. SET @STR='PANKAJ';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT RIGHT(@STR,@INT) [STRING] , @INT [LENGTH]   
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
DROP TABLE image
 
 
RTRIM
 
 
The RTRIM function returns a character string after truncating all trailing blanks.
 
 
Syntax
 
 
RTRIM ( character_expression )
 
 
Return Type
 
 
varchar or nvarchar
 
 
Return Type image
 
 
Example
 
  1. DECLARE @LTRIM varchar(60);  
  2. DECLARE @RTRIM varchar(60);  
  3. SET @LTRIM = ' I HAVE 6 SPACE ON LEFT SIDE';  
  4. SET @RTRIM='I HAVE 6 SPACE ON RIGHT SIDE ';  
  5. SELECT 'STRING WITHOUT RIGHTSPACE ' +  
  6. RTRIM(@RTRIM)+ ' METHOD' UNION ALL  
  7. SELECT 'SPACE OF LEFTTSIDE NOT REMOVE ' +  
  8. RTRIM(@LTRIM)+'REMOVE';  
  9. GO  
Output
 
 
RTRIM
 
 
SPACE
 
 
The SPACE function returns a string of repeated spaces.
 
 
Syntax
 
 
SPACE ( integer_expression )
 
 
Return Type
 
 
varchar
 
 
SPACE
 
 
Example
 
  1. DECLARE @LTRIM varchar(60);  
  2. DECLARE @RTRIM varchar(60);  
  3. SET @LTRIM = 'I HAVE';  
  4. SET @RTRIM='SPACE';  
  5. SELECT @LTRIM+SPACE(4)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(4)))+ SPACE(2)+ @RTRIM AS [STATEMENT] UNION ALL  
  6. SELECT @LTRIM+SPACE(6)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(6)))+ SPACE(2)+ @RTRIM UNION ALL  
  7. SELECT @LTRIM+SPACE(2)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(2)))+ SPACE(2)+ @RTRIM UNION ALL  
  8. SELECT @LTRIM+SPACE(5)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(5)))+SPACE(2)+ @RTRIM  
  9. GO  
Output
 
 
LTRIM
 
 
STR
 
 
The STR function returns character data converted from numeric data.
 
 
Syntax
 
 
STR ( float_expression [ , length [ , decimal ] ] )
 
 
Return Type
 
 
varchar
 
 
STR
 
 
Example
 
  1. DECLARE @FLOAT varchar(60);  
  2. SET @FLOAT=12345.12345;  
  3.   
  4. SELECT STR(@FLOAT,11,5) [STR] ,'FULL STRING'[DISCRIPTION] UNION ALL  
  5. SELECT STR(@FLOAT,11,3) [STR] ,'STRING CONTAIN 2 BLANK SPACE ' UNION ALL  
  6. SELECT STR(@FLOAT,11,1) [STR] ,'STRING CONTAIN 4 BLANK SPACE ' UNION ALL   
  7. SELECT STR(@FLOAT,4,1) [STR] ,'STRING CONVERT INTO *' UNION ALL  
  8. SELECT STR(@FLOAT,4,1) [STR] ,'STRING CONVERT INTO *'   
  9. GO  
Output
 
 
expression
 
 
Note: When the expression exceeds the specified length, the string returns ** for the specified length.
 
 
STUFF
 
 
The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.
 
 
Syntax
 
 
STUFF ( character_expression , start , length , replaceWith_expression )
 
 
Return Type
 
 
Returns character data if character_expression is one of the supported character data types. Returns binary data if character_expression is one of the supported binary data types.
 
 
STUFF
 
 
Example
 
  1. DECLARE @STRING [nvarchar](MAX);  
  2. DECLARE @REPLACE_WITH [nvarchar](MAX);  
  3.   
  4.   
  5. SET @STRING='ABCDEFGHIJKLMNOP';  
  6. SET @REPLACE_WITH='ZZZ'  
  7.   
  8. SELECT STUFF(@STRING,1,4,@REPLACE_WITH) [RESULT] UNION ALL  
  9. SELECT STUFF(@STRING,3,5,@REPLACE_WITH) [RESULT] UNION ALL   
  10. SELECT STUFF(@STRING,4,7,@REPLACE_WITH) [RESULT] UNION ALL   
  11. SELECT STUFF(@STRING,2,2,@REPLACE_WITH) [RESULT] UNION ALL   
  12. SELECT STUFF(@STRING,5,3,@REPLACE_WITH) [RESULT]   
Output
 
 
Output result
 
 
SUBSTRING
 
 
The SUBSTRING function returns part of a character, binary, text, or image expression in SQL Server.
 
 
Syntax
 
 
SUBSTRING ( expression ,start , length )
 
 
Return Type
 
 
character or binary
 
 
SUBSTRING
 
 
Example
 
  1. DECLARE @STRING [nvarchar](MAX);  
  2. SET @STRING='ABCDEFGHIJKLMNOP';  
  3. SELECT SUBSTRING(@STRING,1,3) [STRING] UNION ALL  
  4. SELECT SUBSTRING(@STRING,3,4) [STRING] UNION ALL  
  5. SELECT SUBSTRING(@STRING,2,5) [STRING] UNION ALL  
  6. SELECT SUBSTRING(@STRING,7,4) [STRING] UNION ALL  
  7. SELECT SUBSTRING(@STRING,6,5) [STRING]   
Output
 
 
result
 
 
UPPER
 
 
The UPPER function returns a character expression with lowercase character data converted to uppercase.
 
 
Syntax
 
 
UPPER ( character_expression )
 
 
Return Type
 
 
varchar or nvarchar
 
 
UPPER
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [CHAR] [varchar](MAX),  
  6.   
  7. )  
  8. SET @STR='pankaj';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT UPPER(LEFT(@STR,@INT)) [STRING]   
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
TAB
 
 
UNICODE
 
 
The UNICODE function returns the integer value, as defined by the Unicode standard, for the first character of the input expression.
 
 
Syntax
 
 
UNICODE ( 'ncharacter_expression' )
 
 
Return Type
 
 
int
 
 
UNICODE
 
 
Example
 
  1. DECLARE @STR [varchar](MAX);  
  2. DECLARE @INT INT;  
  3. CREATE TABLE #TAB  
  4. (  
  5. [UNICODE] INT,  
  6. [CHAR] [char](1)  
  7. )  
  8. SET @STR='P@#K/J^%';  
  9. SET @INT=1;  
  10. WHILE @INT<=DATALENGTH(@STR)  
  11. BEGIN  
  12. INSERT INTO #TAB   
  13. SELECT UNICODE(SUBSTRING(@STR,@INT,1)) ,SUBSTRING(@STR,@INT,1)  
  14. SET @INT=@INT+1;  
  15. END  
  16. SELECT * FROM #TAB t  
  17. DROP TABLE #TAB;  
Output
 
 
DROP TABLE
 
 
SOUNDX
 
 
The SOUND function returns a four-character (SOUNDEX) code to evaluate the similarity of two strings. SOUNDEX converts an alphanumeric string to a four-character code that is based on how the string sounds when spoken. The first character of the code is the first character of character_expression, converted to upper case. The second through fourth characters of the code are numbers that represent the letters in the expression. The letters A, E, I, O, U, H, W and Y are ignored unless they are the first letter of the string. The DIFFERENCE function does a SOUNDEX on two strings and returns an integer that represents how similar the SOUNDEX codes are for those strings.
 
 
Syntax
 
 
SOUNDEX ( character_expression )
 
 
Return type
 
 
varchar
 
 
SOUNDX
 
 
Example
 
  1. SELECT SOUNDEX('INDIA') [SONDEX CODE]UNION ALL  
  2. SELECT SOUNDEX('RAJASTHAN'UNION ALL  
  3. SELECT SOUNDEX('ALWAR'UNION ALL  
  4. SELECT SOUNDEX('CITY')  
Output
 
 
Output
 
 
DIFFERENCE
 
 
The DIFFERENCE function returns an integer value that indicates the difference between the SOUNDEX values of two character expressions. The integer returned is the number of characters in the SOUNDEX values that are the same. The return value ranges from 0 through 4. 0 indicates weak or no similarity and 4 indicates strong similarity or the same values.
 
 
Syntax
 
 
DIFFERENCE ( character_expression , character_expression )
 
 
Return Type
 
 
int character_expression
 
 
Is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.
 
 
Return Type
 
 
Example
 
  1. SELECT SOUNDEX('GREEN') [SONDEX CODE1], SOUNDEX('GREENE') [SONDEX CODE1] , DIFFERENCE('GREEN','GREENE') [DEIFFERENCE] UNION ALL  
  2. SELECT SOUNDEX('INDIA') [SONDEX CODE1], SOUNDEX('INDIAAAA') [SONDEX CODE1] , DIFFERENCE('INDIA','INDIAAA') [DEIFFERENCE] UNION ALL  
  3. SELECT SOUNDEX('PANKAJ') [SONDEX CODE1], SOUNDEX('PANKJ') [SONDEX CODE1] , DIFFERENCE('PANKAJ','PANKJ') [DEIFFERENCE]   
Output
 
 
DIFFERENCE
 
 
Thanks for reading this article.