Post
Ask Question

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 believe 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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] CHAR,  
[ASCII] INT,  
)  
SET @STR='PANkaj';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT SUBSTRING(@STR,@INT,1) AS [CHAR] , ASCII(SUBSTRING(@STR,@INT,1)) [ASCII]  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] CHAR,  
[ASCII] INT,  
)  
SET @STR='PANkaj';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT CHAR(ASCII(SUBSTRING(@STR,@INT,1))) [CHAR] , ASCII(SUBSTRING(@STR,@INT,1)) AS [ASCII]   
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @expressionToSearch [varchar](MAX);  
DECLARE @expressionToFind [varchar](MAX);  
SET @expressionToSearch ='SQL SERVER CONTAIN STRING FUNCTION'  
SET @expressionToFind='SERVER';  
  
SELECT CHARINDEX(@expressionToFind,@expressionToSearch ) AS LOCATION_IS;  

Output

expressionToSearc

Example

DECLARE @expressionToSearch [varchar](MAX);  
DECLARE @expressionToFind [varchar](MAX);  
SET @expressionToSearch ='SQL SERVER CONTAIN STRING FUNCTION SERVER'  
SET @expressionToFind='SERVER';  
  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] [varchar](MAX),  
[LENGTH] INT,  
)  
SET @STR='PANKAJ';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT LEFT(@STR,@INT) [STRING] , @INT [LENGTH]   
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] [varchar](MAX),  
[LENGTH] INT,  
)  
SET @STR='PANKAJ';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT LEFT(@STR,@INT) [STRING] , LEN(LEFT(@STR,@INT))  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] [varchar](MAX),  
  
)  
SET @STR='PANKAJ';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT LOWER(LEFT(@STR,@INT)) [STRING]   
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @LTRIM varchar(60);  
DECLARE @RTRIM varchar(60);  
SET @LTRIM = ' I HAVE 6 SPACE ON LEFT SIDE';  
SET @RTRIM='I HAVE 6 SPACE ON RIGHT SIDE ';  
SELECT 'STRING WITHOUT LEFTSPACE ' +  
LTRIM(@LTRIM) UNION ALL  
SELECT 'SPACE OF RIGHTSIDE NOT REMOVE ' +  
LTRIM(@RTRIM)+'REMOVE';  
GO  

Output

LTRIM image

NCHAR

The NCHAR function returns the Unicode character with the specified integer code 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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
  
VALUE INT,  
[CHAR] [nvarchar](MAX)  
)  
SET @STR='P#n()J@';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT UNICODE(SUBSTRING(@STR,@INT,1)) [STRING] , NCHAR(UNICODE(SUBSTRING(@STR,@INT,1))) [VALUE]  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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 way is not found, on all valid text and character data types.

Syntax

PATINDEX ( '%pattern%' , expression )

Return Type

bigint if the expression is of the varchar(max) or nvarchar(max) data types; otherwise, int.

PATINDEX

Example

DECLARE @PATSTRING [nvarchar](MAX);  
DECLARE @PATTERN [nvarchar](MAX);  
SET @PATSTRING='I HAVE A PATTERN STRING';  
  
SELECT PATINDEX('%PA%', @PATSTRING) [INDEX], 'PA' PATTERN UNION ALL  
  
SELECT PATINDEX('%P_T%', @PATSTRING) [INDEX], 'P_T' PATTERN UNION ALL  
  
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

DECLARE @PATTERN [nvarchar](MAX);  
DECLARE @FIND [nvarchar](MAX);  
DECLARE @REPLACEWITH [nvarchar](MAX);  
  
SET @PATTERN='I LIKE ENGLISH';  
SET @FIND='ENGLISH';  
SET @REPLACEWITH='HINDI';  
  
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

DECLARE @QUTO1 [nvarchar](MAX);  
DECLARE @QUTO2 [nvarchar](MAX);  
DECLARE @QUTO3 [nvarchar](MAX);  
  
SET @QUTO1='HINDI';  
SET @QUTO2='HI[NDI';  
SET @QUTO3='HI[]]]''NDI';  
  
SELECT QUOTENAME(@QUTO1) [QUTONMAE] UNION ALL  
SELECT QUOTENAME(@QUTO2) [QUTONMAE] UNION ALL  
SELECT QUOTENAME(@QUTO3) [QUTONMAE]  

Output

QUOTENAME

REVERSE

The REVERSE function Returns the reverse order of a string value.

Syntax

REVERSE ( string_expression )

Return Type

varchar or nvarchar

REVERSE

Example

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[STRING] [varchar](MAX),  
[REVERSE] [varchar](MAX)  
)  
SET @STR='PANKAJ';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT LEFT(@STR,@INT) [STRING] , REVERSE(LEFT(@STR,@INT) ) [REVERSE]  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[STRING] [varchar](MAX),  
  
)  
SET @STR='DEMO';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT REPLICATE('0',@INT)+@STR;  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] [varchar](MAX),  
[LENGTH] INT,  
)  
SET @STR='PANKAJ';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT RIGHT(@STR,@INT) [STRING] , @INT [LENGTH]   
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @LTRIM varchar(60);  
DECLARE @RTRIM varchar(60);  
SET @LTRIM = ' I HAVE 6 SPACE ON LEFT SIDE';  
SET @RTRIM='I HAVE 6 SPACE ON RIGHT SIDE ';  
SELECT 'STRING WITHOUT RIGHTSPACE ' +  
RTRIM(@RTRIM)+ ' METHOD' UNION ALL  
SELECT 'SPACE OF LEFTTSIDE NOT REMOVE ' +  
RTRIM(@LTRIM)+'REMOVE';  
GO  

Output

RTRIM

SPACE

The SPACE function returns a string of repeated spaces.

Syntax

SPACE ( integer_expression )

Return Type

varchar

SPACE

Example

DECLARE @LTRIM varchar(60);  
DECLARE @RTRIM varchar(60);  
SET @LTRIM = 'I HAVE';  
SET @RTRIM='SPACE';  
SELECT @LTRIM+SPACE(4)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(4)))+ SPACE(2)+ @RTRIM AS [STATEMENT] UNION ALL  
SELECT @LTRIM+SPACE(6)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(6)))+ SPACE(2)+ @RTRIM UNION ALL  
SELECT @LTRIM+SPACE(2)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(2)))+ SPACE(2)+ @RTRIM UNION ALL  
SELECT @LTRIM+SPACE(5)+ CONVERT([nvarchar](MAX), DATALENGTH(SPACE(5)))+SPACE(2)+ @RTRIM  
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

DECLARE @FLOAT varchar(60);  
SET @FLOAT=12345.12345;  
  
SELECT STR(@FLOAT,11,5) [STR] ,'FULL STRING'[DISCRIPTION] UNION ALL  
SELECT STR(@FLOAT,11,3) [STR] ,'STRING CONTAIN 2 BLANK SPACE ' UNION ALL  
SELECT STR(@FLOAT,11,1) [STR] ,'STRING CONTAIN 4 BLANK SPACE ' UNION ALL   
SELECT STR(@FLOAT,4,1) [STR] ,'STRING CONVERT INTO *' UNION ALL  
SELECT STR(@FLOAT,4,1) [STR] ,'STRING CONVERT INTO *'   
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.

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

DECLARE @STRING [nvarchar](MAX);  
DECLARE @REPLACE_WITH [nvarchar](MAX);  
  
  
SET @STRING='ABCDEFGHIJKLMNOP';  
SET @REPLACE_WITH='ZZZ'  
  
SELECT STUFF(@STRING,1,4,@REPLACE_WITH) [RESULT] UNION ALL  
SELECT STUFF(@STRING,3,5,@REPLACE_WITH) [RESULT] UNION ALL   
SELECT STUFF(@STRING,4,7,@REPLACE_WITH) [RESULT] UNION ALL   
SELECT STUFF(@STRING,2,2,@REPLACE_WITH) [RESULT] UNION ALL   
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

DECLARE @STRING [nvarchar](MAX);  
SET @STRING='ABCDEFGHIJKLMNOP';  
SELECT SUBSTRING(@STRING,1,3) [STRING] UNION ALL  
SELECT SUBSTRING(@STRING,3,4) [STRING] UNION ALL  
SELECT SUBSTRING(@STRING,2,5) [STRING] UNION ALL  
SELECT SUBSTRING(@STRING,7,4) [STRING] UNION ALL  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[CHAR] [varchar](MAX),  
  
)  
SET @STR='pankaj';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT UPPER(LEFT(@STR,@INT)) [STRING]   
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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

DECLARE @STR [varchar](MAX);  
DECLARE @INT INT;  
CREATE TABLE #TAB  
(  
[UNICODE] INT,  
[CHAR] [char](1)  
)  
SET @STR='P@#K/J^%';  
SET @INT=1;  
WHILE @INT<=DATALENGTH(@STR)  
BEGIN  
INSERT INTO #TAB   
SELECT UNICODE(SUBSTRING(@STR,@INT,1)) ,SUBSTRING(@STR,@INT,1)  
SET @INT=@INT+1;  
END  
SELECT * FROM #TAB t  
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 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 code's second through fourth characters are numbers representing 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 representing how similar the SOUNDEX codes are for those strings.

Syntax

SOUNDEX ( character_expression )

Return type

varchar

SOUNDX

Example

SELECT SOUNDEX('INDIA') [SONDEX CODE]UNION ALL  
SELECT SOUNDEX('RAJASTHAN') UNION ALL  
SELECT SOUNDEX('ALWAR') UNION ALL  
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 substantial similarity or the same values.

Syntax

DIFFERENCE ( character_expression , character_expression )

Return Type

int character_expression

It is an alphanumeric expression of character data. character_expression can be a constant, variable, or column.

Return Type

Example

SELECT SOUNDEX('GREEN') [SONDEX CODE1], SOUNDEX('GREENE') [SONDEX CODE1] , DIFFERENCE('GREEN','GREENE') [DEIFFERENCE] UNION ALL  
SELECT SOUNDEX('INDIA') [SONDEX CODE1], SOUNDEX('INDIAAAA') [SONDEX CODE1] , DIFFERENCE('INDIA','INDIAAA') [DEIFFERENCE] UNION ALL  
SELECT SOUNDEX('PANKAJ') [SONDEX CODE1], SOUNDEX('PANKJ') [SONDEX CODE1] , DIFFERENCE('PANKAJ','PANKJ') [DEIFFERENCE]   

Output

DIFFERENCE

Conclusion

This article taught us about String Functions with different types and code examples in SQL Server.


Similar Articles