SQL Server String Functions-6

A string function accepts a string value as an input and returns a string value regardless of the data type. They’re used to convert an existing string value to a different format, change the letter cases, retrieve a portion of it, or determine the length of it. They are utilized in reports, logic implementation, and data analysis on a daily basis.

This will be a series of postings since we will go over each one in detail with one or more examples for simple comprehension. So continue reading.

For the previous article, click here. In this article, we’ll be discussing the following and the remaining built-in String functions.

SUBSTRING
UNICODE
UPPER

SUBSTRING:
This is a widely used string function by Developers and analysts. It returns part of a character, binary, text, or image expression in SQL Server.

Examples:
SELECT SUBSTRING(‘BigDataNSQL.Com’,1,7);
Returns: BigData
SELECT SUBSTRING(‘BigDataNSQL.Com’,9,3);
Returns: SQL
SELECT SUBSTRING(‘BigDataNSQL.Com’,4,4);
Returns: Data

UNICODE & NCHAR:
UNICODE returns the integer value, as defined by the Unicode standard, for the first character of the input expression. And NCHAR returns the value back to the original character expression.

Example:
DECLARE @nstring NCHAR(12);
SET @nstring = N’Åkergatan 24′;
SELECT UNICODE(@nstring), NCHAR(UNICODE(@nstring));
Returns: 197 Å

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

Example:
SELECT UPPER(‘bigdatansql.com’)
Returns: BIGDATANSQL.COM
SELECT UPPER(‘all capitals’)
Returns: ALL CAPITALS

Click on the function names to refer to the details of the function along with the sample data-set and examples: