Microsoft SQL Server has many built-in functions that can be used to format the output and represents it in the desired format.
In this article, the following built-in string functions supported in Transact SQL will be discussed with examples provided:
Along with that, the following complex examples will be analyzed:
1. How to Remove Duplicate words from the given string.
2. How to Get a count of words within the given string.
3. How to Capitalize each word in the Input String.
Let’s start with T-SQL string functions.
ASCII()
This function returns the ASCII code value as output for a character of the expression.
Syntax
select ASCII('Input_String')
Arguments
It accepts character string as an input.
Example:
The following query returns the ASCII code of character “N”.
select 'N' as [Value], ASCII('N')as [ASCII Code Value]
The output is as follows:
Value ASCII Code Value ----- ---------------- N 78
CHAR()
This function returns a character, an integer, or special character value for given ASCII Code.
Syntax
select CHAR(Input_ASCII_Code)
Arguments
It accepts integer value as an input.
Example
The following query returns the character, numeric and special character for a given ASCII code.
select CHAR(78) [Returned Character],CHAR(55)[Returned Numeric],CHAR(60)[Returned Special Character]
The output is as follows:
Returned Character Returned Numeric Returned Special Character ------------------ ---------------- -------------------------- N 7 <
NCHAR()
This function returns Unicode value for given ASCII Code. This function accepts integer as an input and it returns Unicode value as output for given Ascii code.
Syntax
select NCHAR('Input_ASCII_Code')
Arguments
It accepts integer value as an input. Integer value must be a valid ASCII code.
Example:
The following query returns the Unicode character for a given integer.
Select NCHAR(350)as [Unicode Value]
The output is as follows:
Unicode Value ------------- Ş
CHARINDEX()
This function returns the position of a character or a substring within the given string. If the function finds the character within the given string, then it returns the position of the character, otherwise, it returns NULL.
Syntax
select CHARINDEX(substring_to_search,input_string,[,start_position])
Arguments
1. Substring_to_search
It is the string value / character value that you want to search.
2. Input_string
It is the input string.
3. Start_position
It is the starting position from where the search for the substring/character starts. This is an optional parameter. If we skip this parameter, then the substring/character will be searched for within the entire input string.
Example
The following query gives the position of character ‘R’ for the given string ‘KING IS NISARG’.
Select CHARINDEX('R', 'KING IS NISARG')AS [Character Position in string]
The output is as follows:
Character Position in string ---------------------------- 13
LEFT()
This function returns a specified number of characters in a given string (starting from left).
Syntax
select LEFT(input_string,number_of_characters)
Arguments
1. Input_string
This parameter is the input string or column.
2. Number_of_characters
The total number of characters to be extracted from the given input string or column (Starting from Left).
Example
The following query returns 4 characters ‘NISA’ from the given string ‘NISARG’.
Select LEFT('NISARG', 4)AS [Output of LEFT()]
The output is as follows:
The output of LEFT() ---------------- NISA
RIGHT()
Similarly, this function also returns a specified number of characters in a given string (starting from the right). It uses two input parameters. The first parameter is the number of characters that we want to extract, and the second parameter is a string from which you want to extract the characters.
Syntax
select LEFT(input_string,number_of_characters)
Arguments
1. Input_string
This parameter is the input string or column.
2. Number_of_characters
The total number of characters to be extracted from the given input string or column (Starting from Right).
Example
The following query returns 4 characters ‘SARG’ from the given string ‘NISARG’.
Select RIGHT('NISARG', 4)AS [Output of RIGHT()]
The output is as follows:
The output of RIGHT() ----------------- SARG
LEN()
This function returns the number of characters in a given string.
Syntax
select LEFT(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query returns 11 for the ‘CODINGSIGHT’ string expression.
select len('CODINGSIGHT') as [String Length]
The output is as follows:
String Length ------------- 11
LOWER()
This function returns the lowercased string from the given string.
Syntax
select LOWER(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query will return the ‘sqlserverisawesome’ for the ‘SQLServerIsAwesome’ expression.
select LOWER('SQLServerIsAwesome') as [Lower case string]
The output is as follows:
Lower case string ------------------ sqlserverisawesome
UPPER()
This function returns the uppercased string from the given string.
Syntax
select UPPER(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query will return the ‘SQLSERVERISAWESOME’ for the ‘SQLServerIsAwesome’ expression.
select UPPER('SQLServerIsAwesome') as [Upper case string]
The output is as follows:
Upper case string ------------------ SQLSERVERISAWESOME
LTRIM()
This function removes leading blanks from a given string.
Syntax
select LTRIM(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query returns the ‘SQLServerIsAwesome’ for the ‘ SQLServerIsAwesome ‘ character data.
select LTRIM(' SQLServerIsAwesome') as [Output after removing the blanks]
The output is as follows:
Output after removing the blanks -------------------------------- SQLServerIsAwesome
RTRIM()
This function returns the string after removing trailing blanks in the given string.
Syntax
select RTRIM(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query returns the ‘ SQLServerIsAwesome ‘ for the ‘SQLServerIsAwesome ‘ expression.
select RTRIM('SQLServerIsAwesome ') as [Output after removing the blanks]
The output is as follows:
Output after removing the blanks -------------------------------- SQLServerIsAwesome
REPLACE()
This function returns a string after replacing all the occurrence of specified characters or a substring.
Syntax
select REPLACE(input_string,substring,new_substring)
Arguments
1. Input_string
This parameter is the input string or column.
2. Substring
It is the substring that is to be replaced.
3. New_substring
It is the new replacement substring
Example
The following query replaces the ‘DBA’ string for the ‘Database Administrator’ string data.
SELECT REPLACE('Nisarg is DBA', 'DBA', 'Database Administrator') Result;
The output is as follows:
Result -------------------------------------- Nisarg is Database Administrator
REPLICATE()
This function repeats a string value a specified number of times and returns the result.
Syntax
select REPLICATE(input_string,count)
Arguments
1. Input_string
This parameter is the input string or column.
2. Count
The number of times the string should be repeated.
Example
The following query will give the ‘HELLOHELLOHELLO’ string for the ‘HELLO’ string.
Select REPLICATE('HELLO', 3) as output
The output is as follows:
output --------------- HELLOHELLOHELLO
REVERSE()
This function returns the given string in reverse order.
Syntax
select REVERSE(input_string)
Arguments
Input_string
This parameter is the input string or column.
Example
The following query will return ‘GRASIN’ for the ‘NISARG’ string.
Select REVERSE('NISARG') as output
The output is as follows:
output ------ GRASIN
SPACE()
This function returns a string with the specified number of spaces.
Syntax
select + SPACE(count)
Arguments
Count
This parameter indicates the number of spaces added.
Example
The following query will return the ‘NISARG IS AUTHOR’.
select 'NISARG' + space(1) + 'IS' + space(1) + 'AUTHOR.' as output
The output is as follows:
output ----------------- NISARG IS AUTHOR.
STUFF()
This function first deletes a part of a string and then inserts another part into the string, starting at a specified position.
Syntax
STUFF(Input_string,start_position,length,substring)
Arguments
1. Input_string
It’s a character string to be processed.
2. Start_Position
Position to start the deletion of the specified string
3. Length
The number of characters to be deleted from input_string
4. Substring
It’s a substring that replaces characters specified in “Length” of the “input_string” at “start_position.”
Example
The following query deletes the first six characters (Nisarg) from the input string and inserts the string “Nisarg is Database Administrator.”
SELECT STUFF('Nisarg ', 1 , 6, 'Nisarg is Database Administrator') result;
The output is as follows:
Result --------------------------------- Nisarg is Database Administrator
UNICODE()
This function returns the integer value of the first character in the given string.
Syntax
select UNICODE('Input_String')
Arguments
It accepts character string as an input.
Example
The following query will return 78 for the ‘Nisarg’ input_string.
Select UNICODE('Nisarg')
The output is as follows:
Output ----------- 78
QUOTENAME()
This function returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.
Syntax
select QUOTENAME(Input_String,[delimitor])
Arguments
1. Input_string
This is a sysname, and it has a maximum length of 128 characters. If input_string is more than 128 characters, then it returns null.
2. Delimiter
This parameter is a valid delimiter. This is an optional parameter. If you do not specify any character, then by default, it uses a square bracket ( [] ). Following is the list of valid delimiters
a. A single quotation mark ( ‘ )
b. A left or right square bracket ( [] )
c. A double quotation mark ( ” )
d. A left or right parenthesis ( () )
Example
The QuoteName() function is used in dynamic SQL. To explain this function, first, let me create a table. The table name contains a space. Once the table is created, insert a record in it. To do that, execute the following query:
CREATE TABLE [customer name] ( id INT PRIMARY KEY, Name VARCHAR(255) ); INSERT INTO [customer name] (id, Name) VALUES (1, 'Nisarg Upadhyay...' );
Now execute the query to retrieve output from “customer name” table.
DECLARE @table VARCHAR(128) = 'customer name'; DECLARE @sql NVARCHAR(100) = 'SELECT * FROM ' + @table; EXECUTE (@sql);
The query causes the following error due to the space in the table name
Invalid object name 'customer'.
Now lets use QuoteName() function to make the query valid. To do that, execute the query:
DECLARE @table VARCHAR(128) = 'customer name'; DECLARE @sql NVARCHAR(100) = 'SELECT * FROM ' + QUOTENAME(@table); EXECUTE (@sql);
The output is as follows:
customer_id name ----------- ----------------------- 1 'Nisarg Upadhyay...
FORMAT()
This function returns the input string in the specified format.
Syntax
select FORMAT(Input_String,Format)
Arguments
1. Input_string
Input_string is a valid input.
2. Format
Valid format to convert the input string.
Example
The following query will return the ‘Thursday, August 01, 2019’ for the GETDATE() function. Here specified format ‘D’ refers to weekday name.
SELECT FORMAT ( getdate(), 'D') as Date
The output is as follows:
Date ---------------------------- Thursday, August 1, 2019
CONCAT()
This function joins two or more strings and generates a single string as an output. This function takes up to 255 strings and creates one string.
Syntax
select CONCAT(Input_String_1, Input_String_2, [Input_String_n])
Example
The following query will return the ‘Nisarg Is Database Administrator’ for the given parameters.
select concat ('Nisarg',' ','Is', ' ','Database Administrator') as ConcateString
The output is as follows:
ConcateString -------------------------------- Nisarg Is Database Administrator
Example 1: Removing Duplicate words from the given string
In the first example, I will explain how to remove duplicate words from a string. To achieve that, we will use the following code:
/*INPUT STRING*/
DECLARE @STRING VARCHAR(50) = 'You are talking over and over without any interruption';
SELECT @STRING AS SOURCE,
LTRIM((SELECT DISTINCT ' ' + VALUE AS [TEXT()] /*SPLIT THE STRING AND REMOVE DUPLICATES*/
FROM STRING_SPLIT(@STRING, ' ')
FOR XML PATH('') /*CONCATE ENTIRE STRING*/
)) AS UNIQUEWORDS;
The logic behind the above code is as follows:
1. The SPLIT_STRING function splits the given string into rows based on a delimiter. Read this article for more details of SPLIT_STRING function.
2. Then we use DISTINCT function to remove duplicate values from the input string.
3. Finally, we concatenate the entire string back together using FOR XML PATH(”).
Following is the output of the above script.
Source UniqueWords ------------------------------ -------------------------- You are talking over and over and are over talking You
As you can see from the above output, the duplicate word “Over” has been removed from the input string.
Example 2: Getting a count of words within the given string
In SQL Server, we do not have a built-in function to get the count of the words. In this example, I will explain the logic behind the code which is used to get the word count within the given string. Use the following code:
DECLARE @String VARCHAR(4000) SET @String = 'Nisarg Upadhyay is Database Administrator' SELECT Len(@String) - Len(Replace(@String, ' ', '')) + 1 AS WordCount
The logic of the function is simple. Query performs the following tasks:
1. It gets the length of the input string using the LEN() function.
2. It replaces space from the input string using REPLACE() function and then counts the words in the input string.
The output is as follows:
WordCount ----------- 5
Example 3: Capitalize each word in String
In the first example, I explained how to remove duplicate words from givens string. To capitalize each word in a string, we will use the following code:
DECLARE @WORD_INDEX INT DECLARE @WORD_CHAR CHAR(1) DECLARE @PREVCHAR CHAR(1) DECLARE @OUTPUT_STRING VARCHAR(255) DECLARE @INPUT_STRING VARCHAR(255) SET @INPUT_STRING='nisarg upadhyay is database administrator' SET @OUTPUT_STRING = LOWER(@INPUT_STRING) SET @WORD_INDEX = 1 WHILE @WORD_INDEX <= LEN(@INPUT_STRING) BEGIN SET @WORD_CHAR = SUBSTRING(@INPUT_STRING, @WORD_INDEX, 1) SET @PREVCHAR = CASE WHEN @WORD_INDEX = 1 THEN ' ' ELSE SUBSTRING(@INPUT_STRING, @WORD_INDEX - 1, 1) END IF @PREVCHAR IN ( ' ' ) BEGIN IF @PREVCHAR != '''' OR UPPER(@WORD_CHAR) != 'S' SET @OUTPUT_STRING = STUFF(@OUTPUT_STRING, @WORD_INDEX, 1, UPPER( @WORD_CHAR)) END SET @WORD_INDEX = @WORD_INDEX + 1 END SELECT @Output_String AS [Capitalized First String]
The logic of the above code is as follows:
1. First, we will convert the entire input string in Lower case.
2. Then iterate through each character of the entire input string and if it finds space ‘ ‘ between characters than it will convert the first character of the following word to Upper case.
Following is the output:
Capitalized First String --------------------------------------------- Nisarg Upadhyay Is Database Administrator
As you can see, the first characters of all words within the input string are in upper case.
Summary
In this article, the following issues were covered:
1. Built-in functions supported by Microsoft SQL Server.
2. A few complex examples used to generate the desired output from the table using string functions.
