String Functions in SQL Server

This article explains various string functions in SQL Server.

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 function 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.