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.