T-SQL is not a language designed primarily to do something big with text strings or manipulate with them. That is the reason why we will not find many text-related built-in functions. But we will definitely find use for those that are there. Let’s take a look at SQL String Functions.

Text Concatenation (Combining) – CONCAT Function

Concatenation is one of the most common operations with strings. We have 2 options in TSQL:

• using (+) operator
• applying CONCAT() function

Syntax: CONCAT(text 1, text 2, text 3)

I really recommend to use CONCAT since making strings via (+) operator can be treacherous. If there is a string that is NULL, the result of a combination via (+) operator will always be NULL. This situation can be solved in many different ways. Using the CONCAT function takes no work at all.

Look at an example:

DECLARE @Par1 AS VARCHAR(15) = 'Data Warehouse '
DECLARE @Par2 AS VARCHAR(10) = 'is great'
DECLARE @Par3 AS VARCHAR(10) = NULL

SELECT
CONCAT(@Par1,@Par2,@Par3) AS [Result - CONCAT],
@Par1 + @Par2 + @Par3 AS [Result (+) operator]

Part of String Extraction – SUBSTRING, CHARINDEX, LEN, LEFT, RIGHT FUNCTION

a) SUBSTRING is an ideal candidate to use in cases when we have a text string containing patern – rule. Using SUBSTRING we can easily extract the part of the text that interests us.

Syntax: SUBSTRING(text, starting_position, length)

Example:

DECLARE @Par1 AS VARCHAR(15) = 'BLAH-Hi-BLAH'
SELECT SUBSTRING(@Par1,6,2) AS [Result SUBSTRING]

b) CHARINDEX – We often do not know a position of the first character or we only know that regarding the searched text there is a character from which we want to extract certain number of characters while the position of given character may vary. In this case it is best to combine SUBSTRING function with CHARINDEX which will find us the starting position of the character as a SUBSTRING argument. Similar process will allow us to play with substring argument on number of extracted symbols using this or other SQL string functions.

Syntax: CHARINDEX(Searched string, String to be searched)

Example:

DECLARE @Par1 AS VARCHAR(15) = 'BLAH-Hi-BLAH'

SELECT
CHARINDEX('Hi',@Par1) AS [Starting Position]
,SUBSTRING(@Par1,CHARINDEX('Hi',@Par1),2) AS [Result SUBSTRING with CHARINDEX]

c) LEFT, RIGHT – When we need to extract part of the text starting with first symbol from right or left we can use these functions. Command LEFT(‘xyz’,1) returns first symbol from left, that is x.

Syntax: LEFT(Searched text, number of characters to be extracted)

Finding Out the Length of String – LEN, DATALENGHT Functions

It is sometimes useful to know the length of text string. We can use 2 functions for this purpose – LEN and DATALENGHT

a) LEN function – returns length of entry chain in aspect of symbol count. For example command LEN(xyz) would return value 3. If there are spaces on the end of the text, function will erase them.

Syntax: LEN(Text)

b) DATALENGTH function – returns length from aspect of bytes. This means that if the entry string is Unicode, the function will count 2 bytes for each symbol. For example command DATALENGTH(N’xyz’) will return 6. Function DATALENGTH does not erase spaces as LEN does.

Syntax: DATALENGHT(Text)

Change of Text String – REPLACE, REPLICATE Functions

a) REPLACE Function – If we want to replace previously defined part of text, we use REPLACE. This function replaces previously selected part of the text for a different one. Command REPLACE(‘a.b.c’, ‘.’, ‘-‘) Returns value a-b-c

Syntax: REPLACE(text in which we replace, replaced string, string)

b) REPLICATE Function – We use REPLICATE to repeat particular part of the text by needed number of repetitions. Result of REPLICATE(‘Hi ‘, 3) command will be text Hi Hi Hi

Syntax: REPLICATE(Text, number of repetitions)

Text Strings Formatting Using SQL Functions – UPPER, LOWER, LTRIM, RTRIM

These functions do not need any in-depth explaining. They explain themselves:

1. UPPER – returns text as upper case letters
2. LOWER – returns text as lower case letters
3. LTRIM – deletes spaces on the left
4. RTRIM – deletes spaces on the right

Syntax: FORMAT_FUNCTION(Text)