Transact-SQL: Working With Strings

Total: 1 Average: 5

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

Arguments
It accepts character string as an input.

Example:
The following query returns the ASCII code of character “N”.

The output is as follows:

CHAR()

This function returns a character, an integer, or special character value for given ASCII Code.
Syntax

Arguments
It accepts integer value as an input.

Example
The following query returns the character, numeric and special character for a given ASCII code.

The output is as follows:

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

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.

The output is as follows:

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

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

The output is as follows:

LEFT()

This function returns a specified number of characters in a given string (starting from left).
Syntax

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

The output is as follows:

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

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

The output is as follows:

LEN()

This function returns the number of characters in a given string.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query returns 11 for the ‘CODINGSIGHT’ string expression.

The output is as follows:

LOWER()

This function returns the lowercased string from the given string.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query will return the ‘sqlserverisawesome’ for the ‘SQLServerIsAwesome’ expression.

The output is as follows:

UPPER()

This function returns the uppercased string from the given string.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query will return the ‘SQLSERVERISAWESOME’ for the ‘SQLServerIsAwesome’ expression.

The output is as follows:

LTRIM()

This function removes leading blanks from a given string.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query returns the ‘SQLServerIsAwesome’ for the ‘ SQLServerIsAwesome ‘ character data.

The output is as follows:

RTRIM()

This function returns the string after removing trailing blanks in the given string.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query returns the ‘ SQLServerIsAwesome ‘ for the ‘SQLServerIsAwesome ‘ expression.

The output is as follows:

REPLACE()

This function returns a string after replacing all the occurrence of specified characters or a substring.
Syntax

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.

The output is as follows:

REPLICATE()

This function repeats a string value a specified number of times and returns the result.
Syntax

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.

The output is as follows:

REVERSE()

This function returns the given string in reverse order.
Syntax

Arguments
Input_string
This parameter is the input string or column.

Example
The following query will return ‘GRASIN’ for the ‘NISARG’ string.

The output is as follows:

SPACE()

This function returns a string with the specified number of spaces.
Syntax

Arguments
Count
This parameter indicates the number of spaces added.

Example
The following query will return the ‘NISARG IS AUTHOR’.

The output is as follows:

STUFF()

This function first deletes a part of a string and then inserts another part into the string, starting at a specified position.
Syntax

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

The output is as follows:

UNICODE()

This function returns the integer value of the first character in the given string.
Syntax

Arguments
It accepts character string as an input.

Example
The following query will return 78 for the ‘Nisarg’ input_string.

The output is as follows:

QUOTENAME()

This function returns a Unicode string with delimiters added to make the string a valid SQL Server delimited identifier.
Syntax

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:

Now execute the query to retrieve output from “customer name” table.

The query causes the following error due to the space in the table name

Now lets use QuoteName() function to make the query valid. To do that, execute the query:

The output is as follows:

FORMAT()

This function returns the input string in the specified format.
Syntax

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.

The output is as follows:

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

Example
The following query will return the ‘Nisarg Is Database Administrator’ for the given parameters.

The output is as follows:

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:

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.

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:

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:

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:

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:

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.

Nisarg Upadhyay

Nisarg is a SQL Server Database Administrator and Microsoft certified professional who has more than 5 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the Bachelors in Information Technology from Ganpat University.
Nisarg Upadhyay