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:



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.

• Mathematical Functions In SQL

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



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



CHAR

The CHAR function converts an int ASCII code to a character.

Syntax

CHAR ( integer_expression )

Return Type

char(1)



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



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.



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



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



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.

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



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.



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



LOWER

The LOWER function returns a character expression after converting uppercase character data to lowercase.

Syntax:

LOWER ( character_expression )

Return Type

varchar or nvarchar



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



LTRIM

The LTRIM function returns a character expression after it removes leading blanks.

Syntax

LTRIM ( character_expression )

Return Type

varchar or nvarchar



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



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.



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



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.



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



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.



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



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)



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



REVERSE

REVERSE function Returns the reverse order of a string value.

Syntax

REVERSE ( string_expression )

Return Type

varchar or nvarchar



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



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.



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



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.



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



RTRIM

The RTRIM function returns a character string after truncating all trailing blanks.

Syntax

RTRIM ( character_expression )

Return Type

varchar or nvarchar



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



SPACE

The SPACE function returns a string of repeated spaces.

Syntax

SPACE ( integer_expression )

Return Type

varchar



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



STR

The STR function returns character data converted from numeric data.

Syntax

STR ( float_expression [ , length [ , decimal ] ] )

Return Type

varchar



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



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.



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



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



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



UPPER

The UPPER function returns a character expression with lowercase character data converted to uppercase.

Syntax

UPPER ( character_expression )

Return Type

varchar or nvarchar



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



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



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



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



Example

SELECT SOUNDEX('INDIA') [SONDEX CODE]UNION ALL
SELECT SOUNDEX('RAJASTHAN') UNION ALL
SELECT SOUNDEX('ALWAR') UNION ALL
SELECT SOUNDEX('CITY')

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.



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



Thanks for reading this article.